SELECT
DATE_FORMAT(first_order_time, '%Y-%m') AS 月份,
COUNT(user_phone) AS 总订单,
COUNT(DISTINCT CASE WHEN DATE_FORMAT(final_delivery_time, '%Y-%m') = '2025-01' THEN user_phone END) AS '2025年1月(成交)',
COUNT(DISTINCT CASE WHEN DATE_FORMAT(final_delivery_time, '%Y-%m') = '2025-02' THEN user_phone END) AS '2025年2月(成交)',
COUNT(DISTINCT CASE WHEN DATE_FORMAT(final_delivery_time, '%Y-%m') = '2025-03' THEN user_phone END) AS '2025年3月(成交)',
COUNT(DISTINCT CASE WHEN DATE_FORMAT(final_delivery_time, '%Y-%m') = '2025-04' THEN user_phone END) AS '2025年4月(成交)',
COUNT(DISTINCT CASE WHEN DATE_FORMAT(final_delivery_time, '%Y-%m') = '2025-05' THEN user_phone END) AS '2025年5月(成交)',
COUNT(DISTINCT CASE WHEN DATE_FORMAT(final_delivery_time, '%Y-%m') = '2025-06' THEN user_phone END) AS '2025年6月(成交)',
COUNT(DISTINCT CASE WHEN DATE_FORMAT(final_delivery_time, '%Y-%m') = '2025-07' THEN user_phone END) AS '2025年7月(成交)',
COUNT(DISTINCT CASE WHEN DATE_FORMAT(final_delivery_time, '%Y-%m') = '2025-08' THEN user_phone END) AS '2025年8月(成交)',
COUNT(DISTINCT CASE WHEN DATE_FORMAT(final_delivery_time, '%Y-%m') = '2025-09' THEN user_phone END) AS '2025年9月(成交)',
COUNT(DISTINCT CASE WHEN DATE_FORMAT(final_delivery_time, '%Y-%m') = '2025-10' THEN user_phone END) AS '2025年10月(成交)',
COUNT(DISTINCT CASE WHEN DATE_FORMAT(final_delivery_time, '%Y-%m') = '2025-11' THEN user_phone END) AS '2025年11月(成交)',
COUNT(DISTINCT CASE WHEN DATE_FORMAT(final_delivery_time, '%Y-%m') = '2025-12' THEN user_phone END) AS '2025年12月(成交)'
FROM (
SELECT
o.user_phone,
MIN(o.order_create_time) AS first_order_time,
MAX(CASE WHEN o.order_status IN ('4','41','42','5','8','9','10','12','13','14','15','121','122','131')
THEN o.order_delivery_time END) AS final_delivery_time
FROM orders o
LEFT JOIN orders_info_source s ON s.order_id = o.order_id
LEFT JOIN source c ON c.identify = s.source
WHERE o.user_phone NOT IN (
'13828732621','13530358933','18358889349','13713666003','18038032144','18565399454',
'18683409156','18254701866','18907432501','13430561693','14776231050','13543336115',
'18973513561','18735590366','18823462817','18682291432','18503022713','19129425190',
'17681043696','13544004613','13728731424','15989379809','19854594695','15989379809',
'13590480601','14797635860','16602837041','13536880980','16602837041','17633834697',
'13544004613','18318452072','13138615524','13078687152','15119332972','17724600370',
'13787964520','18681078176','16607486252','18566230353','18824797256','15219857297'
)
AND o.platform = 1
AND o.goods_id <> 6233
AND o.order_type <> '3'
AND o.order_create_time BETWEEN '2025-01-01 00:00' AND '2025-12-31 23:59'
AND o.order_status IN ('4','41','42','5','6','7','71','8','9','10','12','13','14','15','121','122','131')
-- AND c.label LIKE 'A2%'
and (o.deposit_free <> 1 and (o.add_pay_periods <> 0 or nt.order_id is null)) -- 非直发
GROUP BY o.user_phone
) AS user_orders
GROUP BY 月份
ORDER BY 月份;
按月份为分组，左边列是下单的日期，但是下单之后不一定成交，要查询出该订单最后一次下单日期并且成交的，如1月下单的客户有可能是2月才第二次下单成交，有可能是3月再次下单成交的，需要查出第一次下单，最后在哪个月份成交的订单数据
字段：o.order_create_time下单日期，o.order_delivery_time发货日期，o.order_id订单号，o.order_status订单状态， 优化sql写一下完整sql
最新发布
本文介绍了一种从数据库中查询员工考勤记录的方法，包括工号、姓名、组织名称等详细信息，并通过CASE语句实现了对员工情绪状态的分类。
扫一扫
1519
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
