多列GROUP BY结合WHERE与LEFT JOIN的配送站点SQL优化问询

需求规则 #

• 多个订单同delivery_id时仅显示一个站点
• 不同订单的pickup_id与delivery_id相同时合并为一个站点
• 状态为'Picked-up'的订单不生成取货站点

表结构与示例数据 #

orders表 #

addresses表 #

现有问题SQL（无法满足合并规则） #

SELECT 
    a.address_details,
    CASE 
        WHEN o.pickup_id = a.address_id THEN 'Pickup'
        ELSE 'Delivery'
    END AS site_type,
    o.status
FROM orders o
JOIN addresses a ON o.pickup_id = a.address_id OR o.delivery_id = a.address_id
WHERE o.driver_id = 101
AND NOT (o.status = 'Picked-up' AND o.pickup_id = a.address_id)
GROUP BY a.address_id, o.pickup_id, o.delivery_id, o.status;

优化后的查询SQL #

核心思路是先对pickup_id+delivery_id组合分组去重，再关联地址表并过滤不符合规则的站点：

WITH driver_sites AS (
    SELECT 
        driver_id,
        pickup_id,
        delivery_id,
        -- 取该组订单中优先级最高的状态（已完成>运输中>待处理>已取货）
        CASE 
            WHEN MAX(CASE WHEN status = 'Delivered' THEN 4 ELSE 0 END) = 4 THEN 'Delivered'
            WHEN MAX(CASE WHEN status = 'In Transit' THEN 3 ELSE 0 END) = 3 THEN 'In Transit'
            WHEN MAX(CASE WHEN status = 'Pending' THEN 2 ELSE 0 END) = 2 THEN 'Pending'
            ELSE 'Picked-up'
        END AS latest_status
    FROM orders
    WHERE driver_id = 101 -- 指定目标司机ID
    GROUP BY driver_id, pickup_id, delivery_id
)
SELECT 
    a.address_details,
    CASE 
        WHEN ds.pickup_id = a.address_id THEN 'Pickup'
        ELSE 'Delivery'
    END AS site_type,
    ds.latest_status AS status
FROM driver_sites ds
JOIN addresses a ON ds.pickup_id = a.address_id OR ds.delivery_id = a.address_id
-- 过滤状态为Picked-up的取货站点
WHERE NOT (ds.latest_status = 'Picked-up' AND ds.pickup_id = a.address_id)
ORDER BY site_type, status;

这个查询自动满足所有规则：

1. 同delivery_id的订单已通过分组合并
2. 相同pickup_id+delivery_id的组合仅保留一条记录
3. 状态为Picked-up的取货站点被过滤

用COUNT CASE统计站点数量（完全可行） #

基于上面的CTE扩展，即可统计已完成和待完成的站点数量：

WITH driver_sites AS (
    SELECT 
        driver_id,
        pickup_id,
        delivery_id,
        CASE 
            WHEN MAX(CASE WHEN status = 'Delivered' THEN 4 ELSE 0 END) = 4 THEN 'Delivered'
            WHEN MAX(CASE WHEN status = 'In Transit' THEN 3 ELSE 0 END) = 3 THEN 'In Transit'
            WHEN MAX(CASE WHEN status = 'Pending' THEN 2 ELSE 0 END) = 2 THEN 'Pending'
            ELSE 'Picked-up'
        END AS latest_status
    FROM orders
    WHERE driver_id = 101
    GROUP BY driver_id, pickup_id, delivery_id
),
filtered_sites AS (
    SELECT 
        ds.latest_status
    FROM driver_sites ds
    JOIN addresses a ON ds.pickup_id = a.address_id OR ds.delivery_id = a.address_id
    WHERE NOT (ds.latest_status = 'Picked-up' AND ds.pickup_id = a.address_id)
)
SELECT 
    COUNT(CASE WHEN latest_status = 'Delivered' THEN 1 END) AS completed_sites,
    COUNT(CASE WHEN latest_status IN ('In Transit', 'Pending') THEN 1 END) AS pending_sites,
    COUNT(*) AS total_sites
FROM filtered_sites;

内容的提问来源于stack exchange，提问作者Junior Nicolle