SELECT
FROM_UNIXTIME(`addtime`, '%Y-%m-%d') AS time,
COUNT(1) AS '总单量(订单总数)',
SUM( CASE WHEN `status`=1 THEN 1 ELSE 0 END ) AS '已支付(订单总数)',
SUM( CASE WHEN `status`=1 THEN payprice ELSE 0 END ) AS '已收款(订单总价)'
FROM `lm_mtorder`
WHERE FROM_UNIXTIME(`addtime`, '%Y-%m-%d') BETWEEN '2023-06-20' AND '2023-06-25'
GROUP BY time
ORDER BY time DESC
需求：如何对订单支付记录表，获取指定日期之间，每天的的订单分布，比如想获取 2020-08-01到 2020-09-25，每日的订单分布。
如下表结构：
思路如下：
主要是添加时间字段add_time，进行处理，变成日期格式; 然后通过BETWEEN操作符在 WHERE 子句中使用，作用是选取介于两个值之间的数据范围，来匹配日期范围; 接下来通过CASE WHEN exc THEN 1 ELSE 0 END语句进行分类查询获取订单分布情况； 最后通过GROUP BY 对新增处理后的时间time进行分组，按天返回每日订单，和ORDER BY time DESC降序；
SQL写法如下：
add_time 字段是时间戳
SELECT
FROM_UNIXTIME(add_time, '%Y-%m-%d') AS time,
COUNT(1) AS '总单量(订单总数)',
SUM( CASE WHEN pay_status=1 THEN 1 ELSE 0 END ) AS '已支付(订单总数)',
SUM( CASE WHEN pay_status=1 THEN money ELSE 0 END ) AS '已收款(订单总价)',
SUM( CASE WHEN pay_status=0 THEN 1 ELSE 0 END ) AS '已支付(订单总数)',
SUM( CASE WHEN pay_status=0 THEN money ELSE 0 END ) AS '待支付(订单总数)'
FROM `pw_pay_log`
WHERE FROM_UNIXTIME(add_time, '%Y-%m-%d') BETWEEN '2020-08-18' AND '2020-09-11'
GROUP BY time
ORDER BY time DESC;
add_time 字段是Y-m-d H:i:s
SELECT
DATE_FORMAT(create_time, '%Y-%m-%d') AS time,
COUNT(1) AS '总单量(订单总数)',
SUM( CASE WHEN is_status=3 THEN 1 ELSE 0 END ) AS '已支付(订单总数)',
SUM( CASE WHEN is_status=3 THEN member_commission ELSE 0 END ) AS '已收款(订单总价)',
SUM( CASE WHEN is_status=3 THEN 1 ELSE 0 END ) AS '已支付(订单总数)',
SUM( CASE WHEN is_status=3 THEN member_commission ELSE 0 END ) AS '待支付(订单总数)'
FROM `lm_tmpgorder`
WHERE DATE_FORMAT(create_time, '%Y-%m-%d') BETWEEN '2023-06-20' AND '2023-06-25'
GROUP BY time
ORDER BY time DESC最后查询结果如下：
本文共 个字数，平均阅读时长 ≈ 分钟，您已阅读：0时0分0秒。
649494848