Microsoft SQL Azure按ID筛选Hobby字段结果的技术问询

需求说明 #

针对包含ID、Name、Hobby的表，需按以下规则返回数据：

• 若某ID的所有Hobby均为NULL，仅返回该ID的1行记录
• 若某ID的所有Hobby均为具体值，返回该ID的全部记录
• 若某ID的Hobby同时包含NULL和具体值，仅返回该ID中Hobby不为NULL的记录

示例数据 #

期望结果 #

解决方案（适用于Microsoft SQL Azure 12.0.2000.8） #

方法一：窗口函数实现（推荐）

利用窗口函数统计每个ID下非NULL的Hobby数量，以此作为筛选依据：

WITH id_hobby_stats AS (
    SELECT 
        ID,
        Name,
        Hobby,
        -- 统计当前ID下非NULL的Hobby总数
        COUNT(Hobby) OVER(PARTITION BY ID) AS non_null_count
    FROM your_table_name
)
SELECT DISTINCT
    ID,
    Name,
    Hobby
FROM id_hobby_stats
WHERE
    -- 存在非NULL Hobby时，仅保留非NULL行
    (non_null_count > 0 AND Hobby IS NOT NULL)
    -- 无任何非NULL Hobby时，保留所有行（DISTINCT会自动去重为1行）
    OR non_null_count = 0
ORDER BY ID, Hobby;

方法二：EXISTS子查询实现

通过子查询判断每个ID是否存在非NULL的Hobby，再进行筛选：

SELECT 
    ID,
    Name,
    Hobby
FROM your_table_name t
WHERE
    -- 若ID存在非NULL Hobby，仅取非NULL行
    (EXISTS(SELECT 1 FROM your_table_name WHERE ID = t.ID AND Hobby IS NOT NULL) AND Hobby IS NOT NULL)
    -- 若ID无任何非NULL Hobby，取所有行后通过GROUP BY去重
    OR NOT EXISTS(SELECT 1 FROM your_table_name WHERE ID = t.ID AND Hobby IS NOT NULL)
GROUP BY ID, Name, Hobby
ORDER BY ID, Hobby;

方案说明 #

• 两种方法均能满足需求，窗口函数方案性能更优，尤其在数据量较大时
• 针对全NULL的ID，通过DISTINCT或GROUP BY确保仅返回1行记录
• 完全适配Microsoft SQL Azure 12.0.2000.8版本的语法支持

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