对于没有记录的日期,MySQL显示计数为0 [英] MySQL show count of 0 for dates with no records
本文介绍了对于没有记录的日期,MySQL显示计数为0的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我尝试获取当前周(过去7天)的列表中所有用户尝试的COUNT
I'm trying to get the COUNT of all users attempts by a list of the current week (last 7 days)
此查询可以工作,但不会返回0 if日期不存在:
This query works but doesnt return 0 if the day not exist:
SELECT COUNT(*) AS attempt_count,
DATE_FORMAT(attempt_date,'%Y/%m/%d') AS attempt_date
FROM users_attempts
WHERE DATE_SUB(attempt_date, INTERVAL 1 DAY) > DATE_SUB(DATE(NOW()), INTERVAL 1 WEEK)
GROUP BY DAY(attempt_date) DESC;
此查询返回每天最近一周的所有尝试的COUNT,我有这个只有1条记录):
This query return the COUNT of all attempts of the last current week per day, i got this (I only have 1 record):
attempt_count | attempt_date
1 2014/06/19
我想要这个结果:
attempt_count | attempt_date
1 2014/06/19
0 2014/06/18
0 2014/06/17
0 2014/06/16
0 2014/06/15
0 2014/06/14
0 2014/06/13
非常感谢
DEMO : http://sqlfiddle.com/#!2/b58bb/1/0
推荐答案
Ok从我以前的回答从线程 MySql单表,选择最近7天并包含空行
Ok from my previous answer from the thread MySql Single Table, Select last 7 days and include empty rows
这里可以使选择动态日期
Here what you can do for making the date selection dynamic
select
t1.attempt_date,
coalesce(SUM(t1.attempt_count+t2.attempt_count), 0) AS attempt_count
from
(
select DATE_FORMAT(a.Date,'%Y/%m/%d') as attempt_date,
'0' as attempt_count
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
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 union all select 8 union all select 9) as a
cross join (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 union all select 8 union all select 9) as b
cross join (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 union all select 8 union all select 9) as c
) a
where a.Date BETWEEN NOW() - INTERVAL 7 DAY AND NOW()
)t1
left join
(
SELECT DATE_FORMAT(attempt_date,'%Y/%m/%d') AS attempt_date,
COUNT(*) AS attempt_count
FROM users_attempts
WHERE DATE_SUB(attempt_date, INTERVAL 1 DAY) > DATE_SUB(DATE(NOW()), INTERVAL 1 WEEK)
GROUP BY DAY(attempt_date) DESC
)t2
on t2.attempt_date = t1.attempt_date
group by DAY(t1.attempt_date)
order by t1.attempt_date desc;
DEMO
这篇关于对于没有记录的日期,MySQL显示计数为0的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文