如何筛选同一ID下不同CODE中无匹配VALUE的记录？

需求明确 #

从下表中筛选出同一ID范围内，当前记录的VALUE在其他CODE中完全没有匹配的行：

预期结果：

ID 1, CODE B VALUE 789 NOT FOUND
ID 1, CODE A VALUE 456 NOT FOUND

实现方法 #

方法1：使用NOT EXISTS子查询

直接检查当前记录的VALUE在同ID下的其他CODE中是否存在，不存在则保留：

SELECT 
    CONCAT('ID ', t.ID, ', CODE ', t.CODE, ' VALUE ', t.VALUE, ' NOT FOUND') AS result
FROM 
    your_table t
WHERE 
    NOT EXISTS (
        SELECT 1
        FROM your_table t2
        WHERE 
            t2.ID = t.ID          -- 限定同一ID
            AND t2.CODE != t.CODE -- 排除当前CODE
            AND t2.VALUE = t.VALUE-- 匹配VALUE
    );

方法2：使用窗口函数统计CODE数量

先按ID和VALUE分组，统计该VALUE对应的不同CODE总数，总数为1说明该VALUE只在单个CODE中出现：

WITH value_code_stats AS (
    SELECT 
        ID, CODE, VALUE,
        COUNT(DISTINCT CODE) OVER (PARTITION BY ID, VALUE) AS distinct_code_count
    FROM your_table
)
SELECT 
    CONCAT('ID ', ID, ', CODE ', CODE, ' VALUE ', VALUE, ' NOT FOUND') AS result
FROM value_code_stats
WHERE distinct_code_count = 1;

注意：将上述代码中的your_table替换为你的实际表名即可执行。

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