求助：基于两表的CASE语句实现Atleast_1_Stored列计算

需要完善SQL查询中的最后一列CASE语句，该列名为Atleast_1_Stored，用于判断某一ShipmentID下是否存在至少一个被存储过的Item，若存在则返回“Yes”，否则返回“No”。

本次查询包含4个计算列：

• Shipment_Size：对应ShipmentID下的ItemID数量；
• Shipment_Ready：当该ShipmentID下所有ItemID的Item_status均为“Packed”时，返回“Yes”，否则返回“No”；
• Item_Stored：判断单个Item是否至少被存储过一次，若存在“Stored”状态记录则返回“Yes”，否则返回“No”；
• Atleast_1_Stored：待完善列，需判断该ShipmentID下是否存在至少一个被存储过的Item，若是则该ShipmentID对应的所有行均返回“Yes”，否则返回“No”。

建表与插入数据SQL #

DROP TABLE Shipment_Info; 
DROP TABLE Item_Info;

CREATE TABLE Shipment_Info (
    ShipmentID int,
    ItemID int,
    Item_status varchar(255)
);

CREATE TABLE Item_info (
    ItemID int,
    Item_Status varchar(255)
);

INSERT INTO Shipment_Info (
    ShipmentID,
    ItemID,
    Item_status
) VALUES 
(10001,20001,'Packed'), 
(10002,20002,'Allocated'), 
(10002,20003,'Packed'), 
(10003,20004,'Filled'), 
(10004,20005,'Packed'), 
(10004,20006,'Packed'), 
(10004,20007,'Packed'), 
(10005,20008,'Filled'), 
(10005,20009,'Packed'), 
(10006,20010,'Filled');

INSERT INTO Item_Info (
    ItemID,
    Item_Status
) VALUES 
(20001,'Induct'), 
(20001,'Stock'), 
(20002,'Induct'), 
(20002,'Stock'), 
(20002,'Stored'), 
(20002,'Dock'), 
(20003,'Induct'), 
(20003,'Stock'), 
(20003,'Stored'), 
(20004,'Induct'), 
(20004,'Cancelled'), 
(20004,'Stored'), 
(20005,'Induct'), 
(20005,'Stock'), 
(20005,'Stored'), 
(20006,'Induct'), 
(20006,'Reject'), 
(20006,'Induct'), 
(20006,'Stock'), 
(20007,'Induct'), 
(20007,'Stock'), 
(20007,'Stored'), 
(20007,'Stored'), 
(20008,'Induct'), 
(20008,'Stock'), 
(20008,'Reject'), 
(20009,'Induct'), 
(20009,'Stock'), 
(20009,'Induct'), 
(20009,'Stored'), 
(20010,'Induct'), 
(20010,'Stock');

期望输出结果 #

当前查询代码（含待完善部分） #

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
    , case when exists (select 1 from Item_Info ii where ii.ItemId = si.ItemId and ii.Item_Status = 'Stored') then 'Yes' else 'No' end as Item_Stored
-- 待完善的CASE语句
-- case when sum(exists(select 1 from Item_Info ii where ii.ItemId = si.ItemId and ii.Item_Status = 'Stored')) over (partition by ShipmentID) != 0 then 'Yes' else 'No' end as Atleast_1_stored
-- 待完善部分结束
from Shipment_INFO si
group by ShipmentID, Item_status, ItemID;

解决方案 #

原代码中直接对EXISTS的结果求和是错误的，因为EXISTS返回布尔值，无法直接参与数值运算。我们可以先将单个Item的存储状态转换为数值标记（是=1，否=0），再通过窗口函数对每个ShipmentID分组求和，只要总和大于0就说明该Shipment下有至少一个Item被存储过。

修改后的完整查询代码 #

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
    , case when exists (select 1 from Item_Info ii where ii.ItemId = si.ItemId and ii.Item_Status = 'Stored') then 'Yes' else 'No' end as Item_Stored
    -- 完善后的Atleast_1_Stored列
    , case when 
        sum(case when exists (select 1 from Item_Info ii where ii.ItemId = si.ItemId and ii.Item_Status = 'Stored') then 1 else 0 end) over (partition by ShipmentID) > 0
        then 'Yes' else 'No' end as Atleast_1_Stored
from Shipment_INFO si
group by ShipmentID, Item_status, ItemID;

更清晰的CTE写法 #

如果希望代码逻辑更易读，可以先用CTE预计算每个Item的存储标记：

with Item_Stored_Flag as (
    select 
        ShipmentID, 
        ItemID,
        Item_status,
        case when exists (select 1 from Item_Info ii where ii.ItemId = si.ItemId and ii.Item_Status = 'Stored') then 1 else 0 end as is_stored
    from Shipment_INFO si
)
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,
    case when is_stored = 1 then 'Yes' else 'No' end as Item_Stored,
    case when sum(is_stored) over (partition by ShipmentID) > 0 then 'Yes' else 'No' end as Atleast_1_Stored
from Item_Stored_Flag
group by ShipmentID, Item_status, ItemID, is_stored;

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