统计7天之内所有天数中日志的数量

蚂蚁一般黑

于 2021-01-27 14:15:21 发布

阅读量195

点赞数

分类专栏： sql 文章标签： mysql

本文链接：https://blog.youkuaiyun.com/qq_34295227/article/details/113247644

版权

sql 专栏收录该内容

6 篇文章

订阅专栏

SELECT a.click_date,IFNULL(b.count,0) AS COUNT
FROM (
SELECT CURDATE() AS click_date
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS click_date
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) AS click_date
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS click_date
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) AS click_date
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) AS click_date
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS click_date
) a LEFT JOIN (
SELECT DATE(create_date)  AS datatime, COUNT(*) AS COUNT
FROM iot_alarm
GROUP BY DATE(create_date)
) b ON a.click_date = b.datatime