如何用CASE语句基于关联表为orders表生成唯一的status列

原SQL的问题在于，它针对每个sku单独判断状态后，按order_id和status分组，导致同一订单下不同sku的不同状态会生成多条记录，无法得到每个订单的唯一状态。

要实现需求，需要从订单整体维度进行判断，步骤如下：

1. 对每个订单聚合三个关键判断指标：
   • 是否所有sku都满足received >= ordered
   • 是否存在sku满足0 < received < ordered
   • 是否所有sku的received都为0
2. 按照优先级（complete > partial > open）确定订单最终状态

修正后的SQL代码：

SELECT
    o.order_id,
    CASE
        -- 所有sku都满足received≥ordered，标记为complete
        WHEN MIN(od.received >= od.ordered) = 1 THEN 'complete'
        -- 存在sku部分收货，标记为partial（优先级高于open）
        WHEN MAX(CASE WHEN od.received > 0 AND od.received < od.ordered THEN 1 ELSE 0 END) = 1 THEN 'partial'
        -- 所有sku都未收货，标记为open
        WHEN MIN(od.received = 0) = 1 THEN 'open'
        -- 处理无明细的订单（如果有的话），可根据需求调整
        ELSE 'unknown'
    END AS status
FROM
    orders o
LEFT JOIN orders_details od ON o.order_id = od.order_id
GROUP BY
    o.order_id;

代码说明： #

• MIN(od.received >= od.ordered)：如果所有sku都满足received >= ordered，这个表达式的结果为1，否则为0
• MAX(CASE WHEN od.received > 0 AND od.received < od.ordered THEN 1 ELSE 0 END)：只要有一个sku满足部分收货，结果就为1
• MIN(od.received = 0)：如果所有sku的received都是0，结果为1，否则为0
• CASE语句按优先级判断，确保每个订单只会得到一个唯一的状态

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