SQL Server聚合多列：获取各procedure/condition极值及对应医院

针对从imi..imi_2020表生成13项诊疗项目/病症各自的极值（max_rar、min_rar）及对应医院名称的需求，以下是几种高效的合并实现方案，替代多次视图关联的低效方式：

方法一：窗口函数+条件聚合（推荐，高效简洁） #

利用窗口函数标记每个诊疗项目分组下的极值记录，再通过条件聚合将极值与对应医院名整合到单行，仅需扫描一次表数据。

WITH ranked_records AS (
    SELECT 
        procedure_condition,
        rar,
        hospital_full_name,
        -- 标记当前分组中rar最大的记录（若有并列极值，用RANK()替代ROW_NUMBER()保留所有结果）
        ROW_NUMBER() OVER (PARTITION BY procedure_condition ORDER BY rar DESC) AS rn_max,
        -- 标记当前分组中rar最小的记录
        ROW_NUMBER() OVER (PARTITION BY procedure_condition ORDER BY rar ASC) AS rn_min
    FROM imi..imi_2020
)
SELECT 
    procedure_condition,
    MAX(CASE WHEN rn_max = 1 THEN rar END) AS max_rar,
    -- 若存在并列极值，用STRING_AGG拼接所有对应医院名（适用于SQL Server 2017+）
    MAX(CASE WHEN rn_max = 1 THEN hospital_full_name END) AS max_hospital_full_name,
    MIN(CASE WHEN rn_min = 1 THEN rar END) AS min_rar,
    MAX(CASE WHEN rn_min = 1 THEN hospital_full_name END) AS min_hospital_full_name
FROM ranked_records
GROUP BY procedure_condition;

说明：如果需要保留所有并列极值的医院，将ROW_NUMBER()替换为RANK()，并把MAX(...)改为STRING_AGG(CASE WHEN ... END, ', ')来拼接多个医院名称。

方法二：TOP 1 WITH TIES + 聚合合并 #

通过TOP 1 WITH TIES快速筛选出所有分组的极值记录，再合并后聚合为目标结构，适合需要明确区分极值类型的场景。

WITH extrema_records AS (
    -- 获取所有分组的最大rar记录
    SELECT TOP 1 WITH TIES
        procedure_condition,
        rar,
        hospital_full_name,
        'max' AS extrema_type
    FROM imi..imi_2020
    ORDER BY RANK() OVER (PARTITION BY procedure_condition ORDER BY rar DESC)
    UNION ALL
    -- 获取所有分组的最小rar记录
    SELECT TOP 1 WITH TIES
        procedure_condition,
        rar,
        hospital_full_name,
        'min' AS extrema_type
    FROM imi..imi_2020
    ORDER BY RANK() OVER (PARTITION BY procedure_condition ORDER BY rar ASC)
)
SELECT 
    procedure_condition,
    MAX(CASE WHEN extrema_type = 'max' THEN rar END) AS max_rar,
    STRING_AGG(CASE WHEN extrema_type = 'max' THEN hospital_full_name END, ', ') AS max_hospital_full_name,
    MIN(CASE WHEN extrema_type = 'min' THEN rar END) AS min_rar,
    STRING_AGG(CASE WHEN extrema_type = 'min' THEN hospital_full_name END, ', ') AS min_hospital_full_name
FROM extrema_records
GROUP BY procedure_condition;

方法三：子查询直接获取极值（针对原有视图方案的简化） #

如果原有方案依赖多次视图关联，可改为直接在主查询中用子查询获取每个分组的极值及对应医院，减少视图关联的开销（数据量较小时适用）。

SELECT 
    DISTINCT procedure_condition,
    (SELECT TOP 1 rar FROM imi..imi_2020 t2 WHERE t2.procedure_condition = t1.procedure_condition ORDER BY rar DESC) AS max_rar,
    (SELECT TOP 1 hospital_full_name FROM imi..imi_2020 t2 WHERE t2.procedure_condition = t1.procedure_condition ORDER BY rar DESC) AS max_hospital_full_name,
    (SELECT TOP 1 rar FROM imi..imi_2020 t2 WHERE t2.procedure_condition = t1.procedure_condition ORDER BY rar ASC) AS min_rar,
    (SELECT TOP 1 hospital_full_name FROM imi..imi_2020 t2 WHERE t2.procedure_condition = t1.procedure_condition ORDER BY rar ASC) AS min_hospital_full_name
FROM imi..imi_2020 t1;

性能提示：数据量较大时，优先选择方法一或方法二，它们仅需扫描1-2次原表，远优于多次视图关联的多表扫描逻辑。

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