当套件含非活跃组件时将所有对应行组件总价置空的实现

我需要查询一个套件（Kit）列表，每个套件包含一个或多个组件ID。需求是：若某个套件中存在任何状态为Inactive（非活跃）的组件，则将该套件所有行的CompTotalPrice（组件总价）置为NULL。

原查询代码 #

SELECT
BOMID,
KitID,
KitSalesPrice,
ComponentID 'CompID',
ComponentQty 'CompQty',
ComponentSalesPrice 'CompPrice',
ComponentLifecycle 'CompLifecycle',
CASE
    WHEN ComponentLifecycle = ('Active')
    THEN (SUM(ComponentSalesPrice * ComponentQty) OVER (PARTITION BY BOMID, KitID))
    ELSE NULL
END AS [CompTotalPrice],
FROM KitTable

当前查询输出 #

期望输出 #

解决方案 #

可以通过窗口函数先标记每个套件是否存在非活跃组件，再基于标记控制总价的显示，修改后的SQL如下：

SELECT
    BOMID,
    KitID,
    KitSalesPrice AS KitPrice,
    ComponentID AS CompID,
    ComponentQty AS CompQty,
    ComponentSalesPrice AS CompPrice,
    ComponentLifecycle AS CompLifecycle,
    CASE
        -- 检查套件是否存在非活跃组件，存在则总价置空
        WHEN MAX(CASE WHEN ComponentLifecycle = 'Inactive' THEN 1 ELSE 0 END) OVER (PARTITION BY BOMID, KitID) = 0
        THEN SUM(ComponentSalesPrice * ComponentQty) OVER (PARTITION BY BOMID, KitID)
        ELSE NULL
    END AS CompTotalPrice
FROM KitTable

逻辑说明 #

1. 用MAX(CASE WHEN ComponentLifecycle = 'Inactive' THEN 1 ELSE 0 END) OVER (PARTITION BY BOMID, KitID)生成套件的非活跃组件标记：只要套件里有一个Inactive组件，该标记值为1，否则为0。
2. 外层CASE根据标记值判断：标记为0时计算组件总价，标记为1时返回NULL，确保套件所有行的总价统一置空。

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