Mysql:选择两个日期之间的所有数据 [英] Mysql: Select all data between two dates
问题描述
2009-06-25 75
2009-07-01 100
2009-07-02 120
我有一个mysql查询,两个日期。这是查询:
SELECT data FROM tbl WHERE date BETWEEN date1 AND date2
我的问题是,即使没有一天的数据,我也需要在date1和date2之间获取行。
所以我的查询会错过2009-06-25和2009-07-01之间的空白日期。
我可以在某些方面将这些日期添加为0作为数据吗?
您可以使用经常被称为日历表的概念。 这里是如何创建日历表的好指南在MySql中:
- 创建一些基础架构
CREATE TABLE ints(i INTEGER);
INSERT INTO ints VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)
- 仅适用于100天,添加更多int连接更多
SELECT cal.date,tbl.data
FROM(
SELECT'2009-06- 25'+ INTERVAL ai * 10 + bi DAY as date
FROM ints a JOIN ints b
ORDER BY ai * 10 + bi
)cal LEFT JOIN tbl ON cal.date = tbl.date
WHERE cal.date BETWEEN'2009-06-25'AND'2009-07-01';
您可能想要创建表 cal
的子选项。
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
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
My problem is that I also need to get the rows between date1 and date2 even if there is no data for a day.
So my query would miss the dates that are empty between 2009-06-25 and 2009-07-01.
Can I in some way add these dates with just 0 as data?
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';
You might want to create table cal
instead of the subselect.
这篇关于Mysql:选择两个日期之间的所有数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!