SQL技术求助：如何关联两表并生成第三计算列？

需求说明 #

需通过SQL查询生成3个新增列，前2列基于Shipment_Info表，第三列需关联Item_Info表：

• Shipment_Size：对应ShipmentID下的ItemID数量
• Shipment_Ready：当某ShipmentID下所有ItemID状态均为“Packed”时为“Yes”，否则为“No”
• Item_Stored：若ItemID至少有一次“Stored”操作则为“Yes”，否则为“No”

表结构与示例数据 #

Shipment_Info表 #

包含ShipmentID、ItemID、Item_status三列，ItemID唯一，ShipmentID可重复，Item_status有Allocated、Filled、Packed三种状态，示例数据：

Item_Info表 #

包含ItemID、Operation、Op_time三列，ItemID可重复，记录商品的所有操作及对应时间，示例数据：

理想输出 #

用户现有代码（已实现前两列） #

select ShipmentID,ItemID,
       count(ItemID) over (partition by ShipmentID) Shipment_Size,
       case when 
           sum(case when Item_status='Packed' then 1 else 0 end) OVER (partition by ShipmentID ) =count(ItemID) over (partition by ShipmentID)
       then 'Yes' else 'no' end as Shipment_Ready
from Shipment_INFO
group by ShipmentID,Item_status,ItemID

完整解决方案（新增第三列关联） #

要获取Item_Stored列，只需通过ItemID关联Item_Info表，先统计每个ItemID是否存在“Stored”操作，再关联到主查询即可。以下是优化后的完整SQL：

WITH Item_Stored_Status AS (
    SELECT 
        ItemID,
        CASE WHEN EXISTS (SELECT 1 FROM Item_Info WHERE Item_Info.ItemID = s.ItemID AND Operation = 'Stored') 
             THEN 'Yes' 
             ELSE 'No' 
        END AS Item_Stored
    FROM (SELECT DISTINCT ItemID FROM Shipment_Info) s
)
SELECT 
    si.ShipmentID,
    si.ItemID,
    COUNT(si.ItemID) OVER (PARTITION BY si.ShipmentID) AS Shipment_Size,
    CASE 
        WHEN SUM(CASE WHEN si.Item_status = 'Packed' THEN 1 ELSE 0 END) OVER (PARTITION BY si.ShipmentID) = COUNT(si.ItemID) OVER (PARTITION BY si.ShipmentID)
        THEN 'Yes' 
        ELSE 'No' 
    END AS Shipment_Ready,
    iss.Item_Stored
FROM Shipment_Info si
LEFT JOIN Item_Stored_Status iss ON si.ItemID = iss.ItemID;

代码说明 #

1. CTE部分：从Shipment_Info中取出所有唯一ItemID，通过EXISTS判断每个ItemID在Item_Info中是否有“Stored”操作，生成Item_Stored状态。
2. 主查询：将原查询与CTE结果通过ItemID左关联，确保所有Shipment_Info中的记录都能被保留，最终输出包含三列新增字段的结果。

注：原代码中的group by可去掉，因为窗口函数已完成分区统计，多余分组可能导致数据重复或错误。

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