基于StatusCode条件拆分日期列为两列的SQL查询问题

需求 #

根据StatusCode的取值，将单个Date_stamp列拆分为OrderedDate和ShippedDate两列，把同一OrderID的不同状态日期合并为单条记录。

尝试的错误SQL查询 #

select OrderID, StatusCode, Date_stamp 
from xyz where StatusCode ='Shipped'
left join 
select OrderID, StatusCode, Date_stamp 
from xyz where StatusCode ='Ordered'

注：该查询存在语法错误，缺少连接条件ON，且逻辑未正确关联两部分数据。

现有数据 #

期望结果 #

正确解决方案 #

方法1：条件聚合（推荐，适用于大多数SQL数据库） #

通过分组+CASE WHEN筛选不同状态的日期，同时取当前订单的最新状态：

SELECT
    OrderID,
    -- 优先取Shipped状态，没有则取Ordered
    CASE WHEN MAX(CASE WHEN StatusCode = 'Shipped' THEN 1 ELSE 0 END) = 1
         THEN 'Shipped'
         ELSE 'Ordered' END AS StatusCode,
    MAX(CASE WHEN StatusCode = 'Ordered' THEN Date_stamp END) AS OrderedDate,
    MAX(CASE WHEN StatusCode = 'Shipped' THEN Date_stamp END) AS ShippedDate
FROM xyz
WHERE StatusCode IS NOT NULL -- 过滤无效的null状态行
GROUP BY OrderID;

逻辑说明：

• 按OrderID分组，将同一订单的所有行合并
• 用CASE WHEN分别提取Ordered和Shipped对应的日期，MAX用于忽略null值，保留有效日期
• 通过判断是否存在Shipped状态，确定最终的StatusCode

方法2：自连接（修正你原有的思路） #

将订单表按不同状态自连接，关联条件为OrderID：

SELECT
    COALESCE(s.OrderID, o.OrderID) AS OrderID,
    CASE WHEN s.StatusCode IS NOT NULL THEN s.StatusCode ELSE o.StatusCode END AS StatusCode,
    o.Date_stamp AS OrderedDate,
    s.Date_stamp AS ShippedDate
FROM xyz o
LEFT JOIN xyz s
    ON o.OrderID = s.OrderID
    AND s.StatusCode = 'Shipped'
WHERE o.StatusCode = 'Ordered' -- 以Ordered状态为基准行
AND o.StatusCode IS NOT NULL;

逻辑说明：

• 以Ordered状态的行作为左表，左连接Shipped状态的行
• 用COALESCE处理OrderID的取值，确保不为null
• 通过CASE WHEN判断最终的StatusCode，优先取Shipped状态

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