如何在SQL Server透视查询中筛选含4条数据的health_sys_id

方法一：先筛选符合条件的分组（推荐） #

这种方法先统计每个health_sys_id + CATEGORY组合下是否包含全部4个年份（2018-2021）的记录，只保留符合条件的分组再进行透视，结果更准确，性能也更优：

WITH ValidGroups AS (
    SELECT health_sys_id, CATEGORY
    FROM (
        -- 去重获取每个分组下的有效年份
        SELECT DISTINCT health_sys_id, CATEGORY, ffy
        FROM Query1 
        INNER JOIN LINE_NUM_RANGE3 R ON LineNumber BETWEEN R.LINE_NUM_START AND R.LINE_NUM_END
        WHERE health_sys_id BETWEEN 'HSI00000008' AND 'HSI00001365'
          AND ffy IN (2018, 2019, 2020, 2021)
    ) t
    -- 筛选出包含全部4个年份的分组
    GROUP BY health_sys_id, CATEGORY
    HAVING COUNT(DISTINCT ffy) = 4
)
SELECT * 
FROM (
    SELECT 
        Value, 
        health_sys_id,
        CATEGORY,
        ffy, 
        LineNumber = R.LINE_NUM_START + '-' + R.LINE_NUM_END 
    FROM Query1 
    INNER JOIN LINE_NUM_RANGE3 R ON LineNumber BETWEEN R.LINE_NUM_START AND R.LINE_NUM_END
    -- 关联有效分组，仅保留符合条件的记录
    INNER JOIN ValidGroups v ON Query1.health_sys_id = v.health_sys_id AND Query1.CATEGORY = v.CATEGORY
    WHERE health_sys_id BETWEEN 'HSI00000008' AND 'HSI00001365'
) t 
PIVOT(
    SUM(Value) FOR ffy IN ([2018],[2019],[2020],[2021])
) AS pivot_table 
ORDER BY health_sys_id, category;

方法二：透视后过滤非空列 #

如果你的场景中，缺失年份对应的SUM(Value)会直接显示为NULL，可以在透视完成后直接过滤四个年份列都不为空的记录：

SELECT *
FROM (
    -- 原透视查询逻辑
    SELECT * FROM(
        SELECT Value, health_sys_id,CATEGORY,ffy, LineNumber = R.LINE_NUM_START+'-'+R.LINE_NUM_END 
        FROM Query1 
        INNER JOIN LINE_NUM_RANGE3 R ON LineNumber BETWEEN R.LINE_NUM_START AND LINE_NUM_END 
        WHERE (health_sys_id BETWEEN 'HSI00000008' AND 'HSI00001365')
    ) t 
    PIVOT(SUM(Value) FOR ffy IN ([2018],[2019],[2020],[2021])) AS pivot_table 
) filtered
-- 过滤四个年份都有数据的记录
WHERE [2018] IS NOT NULL 
  AND [2019] IS NOT NULL 
  AND [2020] IS NOT NULL 
  AND [2021] IS NOT NULL
ORDER BY health_sys_id, category;

说明 #

• 你之前尝试的LIMIT不适用于SQL Server，这是MySQL等数据库的语法，SQL Server用TOP但这里不需要限制行数，而是需要分组统计筛选，所以用GROUP BY + HAVING来实现。
• 方法一优先推荐，因为它能确保分组下确实存在四个年份的记录，避免因某个年份的SUM(Value)为0而被误判为有数据的情况。

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