mysql按天分组统计

50 阅读 0 评论 33 点赞

我是靠谱客的博主谦让电源，最近开发中收集的这篇文章主要介绍mysql按天分组统计，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

问题描述：

每个月按每天统计订单数，如果当前没有数据则显示为0；

##31可替换成别的天数，"2021-12-01"统计开始时间，此处是12月份整一个月的数据
SELECT
tt1.days
AS '日期',
tt1.orderNum
AS '已提交订单',
tt2.paidNum
AS '已付款订单',
tt3.closedNum
AS '已关闭订单'
FROM
(SELECT
t1.days as days,
count(t2.order_id) AS orderNum
FROM
(
SELECT @date := DATE_ADD(@date, INTERVAL + 1 DAY) days
FROM
(
SELECT @date := DATE_ADD("2021-12-01", INTERVAL -1 DAY) days
FROM km_mall_order kmo limit 31
) t0
) AS t1
LEFT JOIN km_mall_order t2 ON date_format(t2.create_time, '%Y-%m-%d') = date_format(t1.days, '%Y-%m-%d') AND t2.order_source = 4
GROUP BY t1.days ORDER BY t1.days) tt1
INNER JOIN
(SELECT
t1.days as days,
count(t2.order_id) AS paidNum
FROM
(
SELECT @date := DATE_ADD(@date, INTERVAL + 1 DAY) days
FROM
(
SELECT @date := DATE_ADD("2021-12-01", INTERVAL -1 DAY) days
FROM km_mall_order kmo limit 31
) t0
) AS t1
LEFT JOIN km_mall_order t2 ON date_format(t2.create_time, '%Y-%m-%d') = date_format(t1.days, '%Y-%m-%d') AND t2.order_source = 4 and t2.order_status > 1
GROUP BY t1.days ORDER BY t1.days) tt2 ON tt2.days = tt1.days
INNER JOIN
(SELECT
t1.days as days,
count(t2.order_id) AS closedNum
FROM
(
SELECT @date := DATE_ADD(@date, INTERVAL + 1 DAY) days
FROM
(
SELECT @date := DATE_ADD("2021-12-01", INTERVAL -1 DAY) days
FROM km_mall_order kmo limit 31
) t0
) AS t1
LEFT JOIN km_mall_order t2 ON date_format(t2.create_time, '%Y-%m-%d') = date_format(t1.days, '%Y-%m-%d') AND t2.order_source = 4 and t2.order_status = 0
GROUP BY t1.days ORDER BY t1.days) tt3 ON tt3.days = tt1.days;

执行结果显示

+------------+-----------------+-----------------+-----------------+
| 日期
| 已提交订单
| 已付款订单
| 已关闭订单
|
+------------+-----------------+-----------------+-----------------+
| 2021-12-01 |
5 |
0 |
5 |
| 2021-12-02 |
4 |
1 |
3 |
| 2021-12-03 |
8 |
1 |
7 |
| 2021-12-04 |
0 |
0 |
0 |
| 2021-12-05 |
1 |
1 |
0 |
| 2021-12-06 |
4 |
0 |
4 |
| 2021-12-07 |
2 |
1 |
1 |
| 2021-12-08 |
1 |
1 |
0 |
| 2021-12-09 |
2 |
1 |
1 |
| 2021-12-10 |
2 |
1 |
1 |
| 2021-12-11 |
2 |
1 |
1 |
| 2021-12-12 |
1 |
0 |
1 |
| 2021-12-13 |
3 |
1 |
2 |
| 2021-12-14 |
0 |
0 |
0 |
| 2021-12-15 |
0 |
0 |
0 |
| 2021-12-16 |
0 |
0 |
0 |
| 2021-12-17 |
0 |
0 |
0 |
| 2021-12-18 |
0 |
0 |
0 |
| 2021-12-19 |
0 |
0 |
0 |
| 2021-12-20 |
0 |
0 |
0 |
| 2021-12-21 |
0 |
0 |
0 |
| 2021-12-22 |
0 |
0 |
0 |
| 2021-12-23 |
0 |
0 |
0 |
| 2021-12-24 |
0 |
0 |
0 |
| 2021-12-25 |
0 |
0 |
0 |
| 2021-12-26 |
0 |
0 |
0 |
| 2021-12-27 |
0 |
0 |
0 |
| 2021-12-28 |
0 |
0 |
0 |
| 2021-12-29 |
0 |
0 |
0 |
| 2021-12-30 |
0 |
0 |
0 |
| 2021-12-31 |
0 |
0 |
0 |
+------------+-----------------+-----------------+-----------------+

sql解析

1、@date:=
是定义名为date的变量并赋值(select 后面必须用:=)
2、@date:= DATE_ADD(CURDATE(), INTERVAL + 1 DAY) 按照当前日期，加一天; 如果是-1，则是当前时间减
1天
3、 SELECT @date:= DATE_ADD(CURDATE(), INTERVAL + 1 DAY) FROM 数据库表名
4、 @date:= DATE_ADD(@date, INTERVAL - 1 DAY) DAY
把定义的date变量天数-1（自减）
5、 LIMIT 31 限制一下条数，得到了指定日期往前31天的记录
6、 left join group by t1.days 即按照左表关联业务数据，根据左表的日期分组，即分成了指定的31天数据，有记录就统计条数，没有记录就是0
7、inner join 是统计不同条件下的订单数，然后显示在一起