统计最近一周的sql

最新推荐文章于 2024-01-05 15:53:49 发布

原创最新推荐文章于 2024-01-05 15:53:49 发布 · 356 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#统计最近

数据库专栏收录该内容

30 篇文章

订阅专栏

该博客展示了一段SQL代码，用于统计最近一周的数据。思路是先创建一个包含最近一周每一天的临时表，再用该表与订单详情表进行连表查询，最终得到按日期统计的结果。

SELECT DATE_FORMAT(a.create_time, '%m-%d') AS dates,IFNULL(b.sums,0) AS numbers
FROM (
SELECT CURDATE() AS create_time
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS create_time
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) AS create_time
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS create_time
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) AS create_time
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) AS create_time
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS create_time
) a LEFT JOIN (
SELECT DATE(create_time) AS DATETIME, SUM(number) AS sums
FROM order_detail
GROUP BY DATE(create_time)
) b ON a.create_time = b.datetime;

思路先生成一个临时表包括最近一周的每一天

SELECT CURDATE() AS create_time
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS create_time
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) AS create_time
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS create_time
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) AS create_time
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) AS create_time
UNION ALL
SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS create_time

然后用这个表去连表查询得到最后结果