MySQL多表查询

发布于 2024-08-23 14:09:24 字数 526 浏览 5 评论 0原文

我有一堆按日期排序的数据，每个表只保存一个月的数据。（这样做的原因是为了减少查询时间，我说的是每个月表中的数百万行）

例如。

data_01_2010 holds data from 2010-01-01 to 2010-01-31
data_02_2010 holds data from 2010-02-01 to 2010-02-28

有时我必须根据特定的日期范围查询这些表。现在，如果范围跨越多个月。 2010-01-01 到 2010-02-28 那么我需要查询这两个表。

这可以通过单个查询来实现吗？例如：

SELECT * 
FROM data_01_2010, data_02_2010 
WHERE date BETWEEN '2010-01-01' AND '2010-02-28'

上述查询的问题在于，它表示列日期不明确，因为该列存在于两个表中。（表具有相同的结构）

那么这可以通过单个查询来实现还是我必须单独为每个表查询它？

原文

I have a bunch of data ordered by date and each table holds only one month of data. (The reason for this is to cut down query time, I'm talking about millions of rows in each month table)

For ex.

data_01_2010 holds data from 2010-01-01 to 2010-01-31
data_02_2010 holds data from 2010-02-01 to 2010-02-28

Sometimes I have to query these tables according to a specific date range. Now if the range is across multiple months for ex. 2010-01-01 to 2010-02-28 then I need to query both tables.

Can this be achieved with a single query?
Like for example:

SELECT * 
FROM data_01_2010, data_02_2010 
WHERE date BETWEEN '2010-01-01' AND '2010-02-28'

The problem with the above query is that it says the column date is ambiguous which it is, because the column is present in both table. (tables have the same structure)

So is this achievable with a single query or do I have to query it for each table separately?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

长伴 2024-08-30 14:09:24

这是分区为何如此强大的完美示例。分区允许您在逻辑上将所有记录存储在同一个表中，而不会牺牲查询性能。

在此示例中，您将有一个名为 data 的表（希望您将其命名得比这个更好），并根据 date 列的值对其进行范围分区（再次希望您可以更好地命名此列）。这意味着您可以通过简单的选择来满足您的要求：

SELECT * FROM data WHERE date BETWEEN '2010-01-01' AND '2010-02-28';

在幕后，数据库将仅根据 where 子句访问所需的分区。

参考：
http://dev.mysql.com/doc/refman/5.5/ zh/partitioning.html

This is a perfect example of why partitioning is so powerful. Partitioning allows you to logically store all of your records in the same table without sacrificing query performance.

In this example, you would have one table called data (hopefully you would name it better than this) and range partition it based on the value of the date column (again hopefully you would name this column better). This means that you could meet your requirement by a simple select:

SELECT * FROM data WHERE date BETWEEN '2010-01-01' AND '2010-02-28';

Under the covers, the database will only access the partitions required based on the where clause.

Reference:
http://dev.mysql.com/doc/refman/5.5/en/partitioning.html

回复收藏 0 原文

生寂 2024-08-30 14:09:24

如果您在其他地方执行逻辑来确定需要查询哪些表，并且每个月都有相同的架构，那么您可以只合并这些表：

SELECT *
FROM data_01_2010
WHERE date BETWEEN '2010-01-01' AND '2010-02-28'
UNION ALL
SELECT *
FROM data_02_2010
WHERE date BETWEEN '2010-01-01' AND '2010-02-28'

但是如果您需要查询来计算要合并哪些表，那么您正在冒险进入以下领域：存储过程。

If you do the logic elsewhere to figure out what tables you need to query, and each month has the same schema, you could just union the tables:

SELECT *
FROM data_01_2010
WHERE date BETWEEN '2010-01-01' AND '2010-02-28'
UNION ALL
SELECT *
FROM data_02_2010
WHERE date BETWEEN '2010-01-01' AND '2010-02-28'

But if you need the query to calculate which tables to union, you are venturing into realm of stored procedures.

回复收藏 0 原文

~没有更多了~