MySQL：即使不存在记录，也选择范围内的所有日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1046865/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: Select All Dates In a Range Even If No Records Present
提问by Jason
I have a database of users. I would like to create a graph based on userbase growth. The query I have now is:
我有一个用户数据库。我想创建一个基于用户群增长的图表。我现在的查询是：
SELECT DATE(datecreated), count(*) AS number FROM users
WHERE DATE(datecreated) > '2009-06-21' AND DATE(datecreated) <= DATE(NOW())
GROUP BY DATE(datecreated) ORDER BY datecreated ASC
This returns almostwhat I want. If we get 0 users one day, that day is not returned as a 0 value, it is just skipped and the next day that has at least one user is returned. How can I get something like (psuedo-response):
这几乎返回了我想要的。如果我们一天有 0 个用户，那一天不会作为 0 值返回，它只是被跳过，并返回至少有一个用户的第二天。我怎样才能得到类似（伪响应）：
date1 5
date2 8
date3 0
date4 0
date5 9
etc...
where the dates with zero show up in sequential order with the rest of the dates?
零日期与其余日期按顺序显示在哪里？
Thanks!
谢谢！
回答by Igor Kryltsov
I hope you will figure out the rest.
我希望你能弄清楚剩下的。
select * from (
select date_add('2003-01-01 00:00:00.000', INTERVAL n5.num*10000+n4.num*1000+n3.num*100+n2.num*10+n1.num DAY ) as date from
(select 0 as num
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) n1,
(select 0 as num
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) n2,
(select 0 as num
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) n3,
(select 0 as num
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) n4,
(select 0 as num
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) n5
) a
where date >'2011-01-02 00:00:00.000' and date < NOW()
order by date
With
和
select n3.num*100+n2.num*10+n1.num as date
you will get a column with numbers from 0 to max(n3)*100+max(n2)*10+max(n1)
你会得到一列数字从 0 到 max(n3)*100+max(n2)*10+max(n1)
Since here we have max n3 as 3, SELECT will return 399, plus 0 -> 400 records (dates in calendar).
由于这里我们将最大 n3 设为 3，SELECT 将返回 399，加上 0 -> 400 条记录（日历中的日期）。
You can tune your dynamic calendar by limiting it, for example, from min(date) you have to now().
您可以通过限制动态日历来调整它，例如，从 min(date) 到 now()。
回答by Giac
This is better to do as:
最好这样做：
-- 7 Days:
set @n:=date(now() + interval 1 day);
SELECT qb.day_series as days , COALESCE(col_byte, 0) as Bytes from tbl1 qa
right join (
select (select @n:= @n - interval 1 day) day_series from tbl1 limit 7 ) as qb
on date(qa.Timestamp) = qb.day_series and
qa.Timestamp > DATE_SUB(curdate(), INTERVAL 7 day) order by qb.day_series asc
-- 30 Days:
set @n:=date(now() + interval 1 day);
SELECT qb.day_series as days , COALESCE(col_byte, 0) as Bytes from tbl1 qa
right join (
select (select @n:= @n - interval 1 day) day_series from tbl1 limit 30 ) as qb
on date(qa.Timestamp) = qb.day_series and
qa.Timestamp > DATE_SUB(curdate(), INTERVAL 30 day) order by qb.day_series asc;
or without variable like this:
或者没有像这样的变量：
SELECT qb.day_series as days , COALESCE(col_byte, 0) as Bytes from tbl1 qa
right join (
select curdate() - INTERVAL a.a day as day_series from(
select 0 as a union all select 1 union all select 2 union all
select 3 union all select 4 union all
select 5 union all select 6 union all select 7
) as a ) as qb
on date(qa.Timestamp) = qb.day_series and
qa.Timestamp > DATE_SUB(curdate(), INTERVAL 7 day) order by qb.day_series asc;
回答by zombat
This questionasks the same thing I think. Generally the accepted answer seems to be that you either do it in your application logic (read in what you have into an array, then loop through the array and create the missing dates), or you use temporary tables filled with the dates you wish to join.
这个问题问了我想的同样的事情。通常，公认的答案似乎是您要么在应用程序逻辑中执行此操作（将您拥有的内容读入数组，然后遍历该数组并创建缺少的日期），要么使用填充了您希望的日期的临时表加入。
回答by plitwin
Do a right outer join to a table, call it tblCalendar, that is pre-populated with the dates you wish to report on. And join on the date field.
对表进行右外部联接，称为 tblCalendar，它预先填充了您希望报告的日期。并加入日期字段。
Paul
保罗
回答by DreadPirateShawn
On further thought, something like this should be what you want:
进一步思考，这样的事情应该是你想要的：
CREATE TEMPORARY TABLE DateSummary1 ( datenew timestamp ) SELECT DISTINCT(DATE(datecreated)) as datenew FROM users;
CREATE TEMPORARY TABLE DateSummary2 ( datenew timestamp, number int ) SELECT DATE(datecreated) as datenew, count(*) AS number FROM users
WHERE DATE(datecreated) > '2009-06-21' AND DATE(datecreated) <= DATE(NOW())
GROUP BY DATE(datecreated) ORDER BY datecreated ASC;
SELECT ds1.datenew,ds2.number FROM DateSummary1 ds1 LEFT JOIN DateSummary2 ds2 on ds1.datenew=ds2.datenew;
This gives you all the dates in the first table, and the countsummary data in the second table. You might need to replace ds2.numberwith IF(ISNULL(ds2.number),0,ds2.number)or something similar.
这将为您提供第一个表中的所有日期以及count第二个表中的汇总数据。您可能需要替换ds2.number为IF(ISNULL(ds2.number),0,ds2.number)或类似的东西。
回答by Ganj Khani
Query is:
查询是：
SELECT qb.dy as yourday, COALESCE(count(yourcolumn), 0) as yourcount from yourtable qa
right join (
select curdate() as dy union
select DATE_SUB(curdate(), INTERVAL 1 day) as dy union
select DATE_SUB(curdate(), INTERVAL 2 day) as dy union
select DATE_SUB(curdate(), INTERVAL 3 day) as dy union
select DATE_SUB(curdate(), INTERVAL 4 day) as dy union
select DATE_SUB(curdate(), INTERVAL 5 day) as dy union
select DATE_SUB(curdate(), INTERVAL 6 day) as dy
) as qb
on qa.dates = qb.dy
and qa.dates > DATE_SUB(curdate(), INTERVAL 7 day)
order by qb.dy asc;
and the result is:
结果是：
+------------+-----------+
| yourday | yourcount |
+------------+-----------+
| 2015-06-24 | 274339 |
| 2015-06-25 | 0 |
| 2015-06-26 | 0 |
| 2015-06-27 | 0 |
| 2015-06-28 | 134703 |
| 2015-06-29 | 87613 |
| 2015-06-30 | 0 |
+------------+-----------+
