MySQL 按 dayofweek 分组的行计数，包括零结果

Question

本周我进行了检索每日数据的查询。我希望得到一个如下表:

  hari   total  finish   issue  
 ------  ------  ------  --------
 1       0       0         0
 2       0       0         0
 3       1       0         1
 4       1       1         0
 5       0       0         0
 6       0       0         0
 7       0       0         0

这是我的查询:

SELECT  DAYOFWEEK(`waktu`) AS hari,
COALESCE( (COUNT(*) ), 0) AS total, 
COUNT(IF(`jarak`<70,1,NULL)) AS finish, 
COUNT(IF(`jarak`>70,1,NULL)) AS issue 
FROM `presensi` 
WHERE WEEKOFYEAR(`waktu`)=WEEKOFYEAR(NOW())
GROUP BY hari;

但是，查询不显示零结果。如何显示一周中的每一天，包括带有空数据的一天？

Answer 1

我已经解决了我的问题。我创建了一个包含日期列表的虚拟表(来自@drew-pierce 的想法)。这是我的查询:

SELECT 
  h.`id`, 
  COALESCE(d.total,0) AS total, 
  COALESCE(d.finish,0) AS finish,  
  COALESCE(d.issue,0) AS issue
FROM
  hari_dummy h 
LEFT JOIN 
(
  SELECT 
    DAYOFWEEK(p.waktu) AS hari,
    COUNT(p.waktu) AS total,
    COUNT(IF(p.`jarak` < 70, 1, NULL)) AS finish,
    COUNT(IF(p.`jarak` > 70, 1, NULL)) AS issue 
  FROM
    `presensi` p 
  WHERE 
     WEEKOFYEAR(waktu) = WEEKOFYEAR(NOW()) 
  GROUP BY hari
 ) d 
ON d.hari = h.`id` 
ORDER BY h.id ASC;