Mysql:选择两个日期之间的所有数据

2021-11-20 00:00:00 date between mysql

我有一个 mysql 表，其中包含与日期相关的数据.每行都有数据和日期，如下所示:

I have a mysql table with data connected to dates. Each row has data and a date, like this:

2009-06-25    75
2009-07-01    100
2009-07-02    120

我有一个 mysql 查询，用于选择两个日期之间的所有数据.这是查询:

I have a mysql query that select all data between two dates. This is the query:

SELECT data FROM tbl WHERE date BETWEEN date1 AND date2

我的问题是，即使一天没有数据，我也需要获取 date1 和 date2 之间的行.

My problem is that I also need to get the rows between date1 and date2 even if there is no data for a day.

所以我的查询会错过 2009-06-25 和 2009-07-01 之间的空日期.

So my query would miss the dates that are empty between 2009-06-25 and 2009-07-01.

我可以以某种方式添加这些只有 0 作为数据的日期吗?

Can I in some way add these dates with just 0 as data?

推荐答案

您可以使用一个经常被称为日历表"的概念.这里是关于如何创建日历表的很好的指南在 MySql 中:

You can use a concept that is frequently referred to as 'calendar tables'. Here is a good guide on how to create calendar tables in MySql:

-- create some infrastructure
CREATE TABLE ints (i INTEGER);
INSERT INTO ints VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

-- only works for 100 days, add more ints joins for more
SELECT cal.date, tbl.data
FROM (
    SELECT '2009-06-25' + INTERVAL a.i * 10 + b.i DAY as date
    FROM ints a JOIN ints b
    ORDER BY a.i * 10 + b.i
) cal LEFT JOIN tbl ON cal.date = tbl.date
WHERE cal.date BETWEEN '2009-06-25' AND '2009-07-01';

您可能想要创建表 cal 而不是子选择.

You might want to create table cal instead of the subselect.

相关文章