如何用SQL找出含Inspection_ID 49且缺失指定检测项的芯片记录

我在一家微芯片生产工厂工作，以下是Lot ID为1402361301、MICROCHIP_SERIAL为1的样本数据表：

样本数据表 #

根据规则，STATUS为1且包含Inspection_ID 49的微芯片，必须完成另外11项检测（35、36、37、38、39、40、41、42、43、52、53）。以下是针对不同需求的SQL实现：

1. 找出缺失任意11项中某项的记录 #

该查询会列出符合条件的微芯片，以及它们缺失的具体检测项：

-- 生成必须完成的11项检测ID集合
WITH required_inspections AS (
    SELECT UNNEST(ARRAY[35,36,37,38,39,40,41,42,43,52,53]) AS required_id
),
-- 筛选出STATUS=1且已完成检测49的微芯片
target_chips AS (
    SELECT LOT_ID, MICROCHIP_SERIAL
    FROM your_table_name
    WHERE STATUS = 1 AND INSPECTION_ID = 49
    GROUP BY LOT_ID, MICROCHIP_SERIAL
)
-- 左连接对比，找出缺失项
SELECT 
    tc.LOT_ID,
    tc.MICROCHIP_SERIAL,
    ri.required_id AS missing_inspection_id
FROM target_chips tc
CROSS JOIN required_inspections ri
LEFT JOIN your_table_name t 
    ON tc.LOT_ID = t.LOT_ID 
    AND tc.MICROCHIP_SERIAL = t.MICROCHIP_SERIAL 
    AND ri.required_id = t.INSPECTION_ID
WHERE t.INSPECTION_ID IS NULL;

2. 筛选缺失全部11项的记录 #

基于上述查询统计缺失数量，筛选出缺失数量等于11的微芯片：

WITH required_inspections AS (
    SELECT UNNEST(ARRAY[35,36,37,38,39,40,41,42,43,52,53]) AS required_id
),
target_chips AS (
    SELECT LOT_ID, MICROCHIP_SERIAL
    FROM your_table_name
    WHERE STATUS = 1 AND INSPECTION_ID = 49
    GROUP BY LOT_ID, MICROCHIP_SERIAL
),
missing_counts AS (
    SELECT 
        tc.LOT_ID,
        tc.MICROCHIP_SERIAL,
        COUNT(CASE WHEN t.INSPECTION_ID IS NULL THEN 1 END) AS total_missing
    FROM target_chips tc
    CROSS JOIN required_inspections ri
    LEFT JOIN your_table_name t 
        ON tc.LOT_ID = t.LOT_ID 
        AND tc.MICROCHIP_SERIAL = t.MICROCHIP_SERIAL 
        AND ri.required_id = t.INSPECTION_ID
    GROUP BY tc.LOT_ID, tc.MICROCHIP_SERIAL
)
SELECT LOT_ID, MICROCHIP_SERIAL
FROM missing_counts
WHERE total_missing = 11;

3. 筛选缺失任意1项的记录 #

如果只需要获取存在缺失项的微芯片基本信息，可使用以下查询：

WITH required_inspections AS (
    SELECT UNNEST(ARRAY[35,36,37,38,39,40,41,42,43,52,53]) AS required_id
),
target_chips AS (
    SELECT LOT_ID, MICROCHIP_SERIAL
    FROM your_table_name
    WHERE STATUS = 1 AND INSPECTION_ID = 49
    GROUP BY LOT_ID, MICROCHIP_SERIAL
),
missing_check AS (
    SELECT 
        tc.LOT_ID,
        tc.MICROCHIP_SERIAL,
        BOOL_OR(t.INSPECTION_ID IS NULL) AS has_missing
    FROM target_chips tc
    CROSS JOIN required_inspections ri
    LEFT JOIN your_table_name t 
        ON tc.LOT_ID = t.LOT_ID 
        AND tc.MICROCHIP_SERIAL = t.MICROCHIP_SERIAL 
        AND ri.required_id = t.INSPECTION_ID
    GROUP BY tc.LOT_ID, tc.MICROCHIP_SERIAL
)
SELECT LOT_ID, MICROCHIP_SERIAL
FROM missing_check
WHERE has_missing = TRUE;

4. 筛选缺失特定某一项的记录 #

比如要找出缺失Inspection_ID=35的微芯片，只需修改检测项集合为指定ID：

WITH required_inspections AS (
    SELECT 35 AS required_id -- 指定要检查的缺失项
),
target_chips AS (
    SELECT LOT_ID, MICROCHIP_SERIAL
    FROM your_table_name
    WHERE STATUS = 1 AND INSPECTION_ID = 49
    GROUP BY LOT_ID, MICROCHIP_SERIAL
)
SELECT 
    tc.LOT_ID,
    tc.MICROCHIP_SERIAL
FROM target_chips tc
CROSS JOIN required_inspections ri
LEFT JOIN your_table_name t 
    ON tc.LOT_ID = t.LOT_ID 
    AND tc.MICROCHIP_SERIAL = t.MICROCHIP_SERIAL 
    AND ri.required_id = t.INSPECTION_ID
WHERE t.INSPECTION_ID IS NULL;

兼容说明 #

如果使用MySQL等不支持UNNEST(ARRAY)的数据库，可用UNION ALL生成检测项列表，示例：

SELECT 35 AS required_id UNION ALL
SELECT 36 UNION ALL
SELECT 37 UNION ALL
SELECT 38 UNION ALL
SELECT 39 UNION ALL
SELECT 40 UNION ALL
SELECT 41 UNION ALL
SELECT 42 UNION ALL
SELECT 43 UNION ALL
SELECT 52 UNION ALL
SELECT 53

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