SQL Server查询：过滤存在非NULL父值的子项对应NULL父值行

实现代码 #

可以通过窗口函数快速实现需求，语句如下：

SELECT child, parent
FROM (
    SELECT 
        child,
        parent,
        COUNT(parent) OVER(PARTITION BY child) AS non_null_parent_cnt
    FROM (
        SELECT child, parent
        FROM [table]
        GROUP BY child, parent
    ) AS derivedtbl_1
) AS filter_tbl
WHERE 
    -- 无任何非空父级时保留NULL行，有非空父级时仅保留非空行
    non_null_parent_cnt = 0 
    OR parent IS NOT NULL
ORDER BY child

逻辑说明 #

• COUNT(parent)统计时会自动忽略NULL值，因此窗口函数COUNT(parent) OVER(PARTITION BY child)返回的是每个child对应的非空parent总数
• 过滤条件判断：如果某child的非空父级总数为0，说明该child只有NULL的父级行，直接保留；如果非空父级总数大于0，就只保留parent非空的行，自动过滤掉NULL的父级行
• 语句兼容SQL Server所有支持窗口函数的版本，在17万行数据量级下执行性能无压力

如果你更倾向于用EXISTS写法，也可以用以下等效语句：

WITH child_parent_pairs AS (
    SELECT child, parent
    FROM [table]
    GROUP BY child, parent
)
SELECT a.child, a.parent
FROM child_parent_pairs a
WHERE 
    a.parent IS NOT NULL
    OR NOT EXISTS (
        SELECT 1 
        FROM child_parent_pairs b 
        WHERE b.child = a.child AND b.parent IS NOT NULL
    )
ORDER BY a.child

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