对于没有记录的日期，MySQL 显示计数为 0

2021-12-30 00:00:00 sql date count mysql

我正在尝试通过本周(过去 7 天)的列表获取所有用户尝试的 COUNT

I'm trying to get the COUNT of all users attempts by a list of the current week (last 7 days)

此查询有效，但如果日期不存在则不返回 0:

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;

此查询返回当前最近一周每天所有尝试的计数，我得到了这个(我只有 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

非常感谢

演示:http://sqlfiddle.com/#!2/b58bb/1/0

推荐答案

从我之前从线程中得到的答案 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;

演示

相关文章