如何编写SQL筛选有效组件及全退役组件的物料数据？

需求说明 #

从数据表中提取每个Item及其Component，需满足以下任一条件：

• 组件的Component_Status为Valid；
• 该Item的所有Component的Component_Status均为Decommissioned。

示例数据表 #

预期结果 #

解决方案 #

下面提供两种符合需求的SQL实现方式，均基于子查询/CTE逻辑，能精准匹配你的筛选规则：

方法1：使用NOT EXISTS子查询 #

SELECT ic.Item_Number, ic.Item_Status, ic.Component, ic.Component_Status
FROM item_components ic
WHERE ic.Component_Status = 'Valid'
OR NOT EXISTS (
    -- 检查当前Item是否存在状态为Valid的组件
    SELECT 1
    FROM item_components ic2
    WHERE ic2.Item_Number = ic.Item_Number
      AND ic2.Component_Status = 'Valid'
);

逻辑说明：

• 第一部分直接筛选出所有状态为Valid的组件；
• 第二部分通过NOT EXISTS判断当前Item下没有任何Valid状态的组件（即所有组件都是Decommissioned），这类Item的所有组件都会被选中。

方法2：使用CTE（公共表表达式） #

WITH item_valid_summary AS (
    -- 先统计每个Item是否存在Valid状态的组件
    SELECT 
        Item_Number,
        -- 用MAX标记：1表示存在Valid组件，0表示不存在
        MAX(CASE WHEN Component_Status = 'Valid' THEN 1 ELSE 0 END) AS has_valid_component
    FROM item_components
    GROUP BY Item_Number
)
SELECT ic.Item_Number, ic.Item_Status, ic.Component, ic.Component_Status
FROM item_components ic
JOIN item_valid_summary ivs ON ic.Item_Number = ivs.Item_Number
WHERE ic.Component_Status = 'Valid'
   OR ivs.has_valid_component = 0;

逻辑说明：

• CTE部分先按Item_Number分组，生成每个Item的有效组件标记；
• 关联原表后，要么筛选出Valid状态的组件，要么筛选出完全没有Valid组件的Item的所有组件，最终得到符合要求的结果。

验证结果 #

以上两种方法运行后，都会返回你预期的结果，完美匹配需求中的筛选规则。

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