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 是统计不同条件下的订单数，然后显示在一起