问题描述:
每个月按每天统计订单数,如果当前没有数据则显示为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 是统计不同条件下的订单数,然后显示在一起