按区域统计符合条件的工单历史：修复SQL计数逻辑问题

问题分析 #

原SQL存在两个核心问题：

• 未筛选出必须曾进入待备件状态的工单，导致统计了从未有过wo_status=2就直接关闭的工单，使得closed计数超过need_spares；
• 直接对wo_status=2的记录求和，同一工单多次出现待备件状态会被重复计数。

修复后的SQL #

SELECT 
    d.name AS district_name,
    COUNT(DISTINCT wo.work_order_num) AS need_spares,
    SUM(CASE WHEN wo.has_closed = 1 THEN 1 ELSE 0 END) AS closed
FROM (
    SELECT 
        w.work_order_num,
        w.location,
        -- 标记工单是否曾处于待备件状态
        MAX(CASE WHEN w.wo_status = 2 THEN 1 ELSE 0 END) AS has_need_spares,
        -- 标记工单是否最终已关闭
        MAX(CASE WHEN w.wo_status = 4 THEN 1 ELSE 0 END) AS has_closed
    FROM work_orders_history w
    WHERE w.created_at BETWEEN '2022-09-19 00:00:00' AND '2022-11-30 23:59:59'
    GROUP BY w.work_order_num, w.location
    -- 仅保留曾进入待备件状态的工单
    HAVING MAX(CASE WHEN w.wo_status = 2 THEN 1 ELSE 0 END) = 1
) wo
LEFT JOIN berc_locations l ON wo.location = l.ID
LEFT JOIN berc_cities c ON l.city_id = c.ID
LEFT JOIN berc_districts d ON c.district_id = d.ID
GROUP BY d.name
ORDER BY closed DESC;

关键修复点说明 #

1. 内层子查询过滤有效工单

   • 按work_order_num和location分组，确保每个工单只被处理一次；
   • 使用MAX(CASE...)判断工单是否出现过wo_status=2（待备件）和wo_status=4（已关闭）的状态；
   • HAVING子句直接排除从未进入待备件状态的工单，解决了55261、55263这类无效工单的统计问题。

2. 外层查询实现精准计数

   • COUNT(DISTINCT wo.work_order_num)统计每个区域内曾进入待备件状态的工单总数，同一工单即使多次出现待备件记录也只计数一次，解决了55262、55264的重复计数问题；
   • SUM(CASE...)统计其中最终已关闭的工单数量，由于外层仅处理曾进入待备件状态的工单，closed计数绝不会超过need_spares。

3. 时间范围优化

   • 将结束时间从00:00:00改为23:59:59，避免漏掉统计日期当天最后时刻的工单记录。

期望输出 #

内容的提问来源于stack exchange，提问作者Muhammad Asif Raza