SQL Create连续日期Even Count为0

Question

我有log表如下所示，我想为我的图表收集log表，我想收集基于category和date的数据，向下浏览我的sql代码

<表类> id 类别 日期 tbody><<tr>2penghapusan2021-08-24 08:23:243penghapusan2021-08-24 08:24:534penerimaan2021-08-24 08:27:575penerimaan2021-08-24 08:28:076penerimaan2021-08-26 08:31:547penolakan2021-08-24 08:34:278penolakan2021-08-24 08:36:169penolakan2021-08-25 08:38:1310penolakan2021-08-25 08:40:0011penolakan2021-08-25 08:40:1312penolakan2021-08-25 08:41:2013pengiriman2021-08-24 08:41:5314penolakan2021-08-24 08:42:1315pengiriman2021-08-24 08:42:2816penerimaan2021-08-26 08:42:3517pengiriman2021-08-25 08:43:0018penerimaan2021-08-26 08:43:0619penerimaan2021-08-27 08:43:0620pengiriman2021-08-25 08:43:3121penolakan2021-08-24 08:43:3722penolakan2021-08-24 08:43:3723pengiriman2021-08-26 08:44:0624penolakan2021-08-24 08:44:1525penolakan2021-08-24 08:44:1526pengiriman2021-08-26 08:47:3627penolakan2021-08-24 08:47:4128pengiriman2021-08-28 08:48:0729penerimaan2021-08-27 08:48:1130pengiriman2021-08-28 08:48:1631penerimaan2021-08-27 08:48:2032penghapusan2021-08-24 08:48:4433pengubahan2021-08-24 08:48:5134pengubahan2021-08-24 08:49:0135penghapusan2021-08-24 08:49:1336penghapusan2021-08-24 08:52:3237pembuatan2021-08-24 08:52:5038pembuatan2021-08-24 08:53:39

您需要将不同的类别与不同的日期进行CROSS连接，然后将LEFT连接到log以聚合所有现有的日期:

SELECT c.category, 
COUNT(l.id) total,  
d.date
FROM (SELECT DISTINCT category FROM log) c
CROSS JOIN (SELECT DISTINCT DATE(date) date FROM log) d
LEFT JOIN log l ON l.category = c.category AND DATE(l.date) = d.date
GROUP BY c.category, d.date
ORDER BY c.category ASC
LIMIT 100;

注意，如果所有类别的表中都缺少一个日期，则不会在所有类别的结果中显示为0。