如何按CaseID与NAME分组，聚合ROLE并保留同行ADDRESS和ZIP

原始数据如下：

需求是按CaseID和NAME分组，将每组内的ROLE合并为逗号分隔的列表。但难点在于：以Bill为例，同一分组下存在不同的ADDRESS和ZIP，如果直接用MAX()或MIN()聚合，可能会出现ADDRESS和ZIP来自不同行的情况，导致数据不一致。需要在分组聚合ROLE的同时，获取同一行的ADDRESS和ZIP（任意一行均可），预期结果如下两种之一：

预期结果一 #

预期结果二 #

核心思路是先为每个(CaseID, NAME)分组锁定任意一行的ADDRESS和ZIP，再对ROLE进行聚合。以下是不同SQL方言的实现方式：

1. MySQL/MariaDB #

方法一：窗口函数+关联聚合 #

用ROW_NUMBER()标记每组内的行，筛选出每组第一行后关联原表聚合ROLE：

WITH ranked_data AS (
    SELECT 
        CaseID, NAME, ADDRESS, ZIP, ROLE,
        ROW_NUMBER() OVER (PARTITION BY CaseID, NAME ORDER BY (SELECT NULL)) AS rn
    FROM your_table_name
)
SELECT 
    rd.CaseID, rd.NAME, rd.ADDRESS, rd.ZIP,
    GROUP_CONCAT(r.ROLE SEPARATOR ', ') AS ROLE
FROM ranked_data rd
JOIN your_table_name r ON rd.CaseID = r.CaseID AND rd.NAME = r.NAME
WHERE rd.rn = 1
GROUP BY rd.CaseID, rd.NAME, rd.ADDRESS, rd.ZIP;

方法二：ANY_VALUE()函数（MySQL 8.0+） #

直接用ANY_VALUE()选取分组内某一行的字段值，保证地址和邮编来自同一行：

SELECT 
    CaseID, NAME,
    ANY_VALUE(ADDRESS) AS ADDRESS,
    ANY_VALUE(ZIP) AS ZIP,
    GROUP_CONCAT(ROLE SEPARATOR ', ') AS ROLE
FROM your_table_name
GROUP BY CaseID, NAME;

2. PostgreSQL #

方法一：DISTINCT ON+关联聚合 #

用DISTINCT ON快速获取每组第一行的地址和邮编，再关联原表聚合ROLE：

WITH first_row_per_group AS (
    SELECT DISTINCT ON (CaseID, NAME)
        CaseID, NAME, ADDRESS, ZIP
    FROM your_table_name
    ORDER BY CaseID, NAME, (SELECT NULL) -- 可自定义排序规则选择特定行
)
SELECT 
    frpg.CaseID, frpg.NAME, frpg.ADDRESS, frpg.ZIP,
    STRING_AGG(t.ROLE, ', ') AS ROLE
FROM first_row_per_group frpg
JOIN your_table_name t ON frpg.CaseID = t.CaseID AND frpg.NAME = t.NAME
GROUP BY frpg.CaseID, frpg.NAME, frpg.ADDRESS, frpg.ZIP;

方法二：窗口函数+聚合 #

用FIRST_VALUE()提前锁定每组的地址和邮编，再聚合ROLE：

WITH grouped_data AS (
    SELECT 
        CaseID, NAME, ADDRESS, ZIP, ROLE,
        FIRST_VALUE(ADDRESS) OVER (PARTITION BY CaseID, NAME ORDER BY (SELECT NULL)) AS first_address,
        FIRST_VALUE(ZIP) OVER (PARTITION BY CaseID, NAME ORDER BY (SELECT NULL)) AS first_zip
    FROM your_table_name
)
SELECT 
    CaseID, NAME, first_address AS ADDRESS, first_zip AS ZIP,
    STRING_AGG(ROLE, ', ') AS ROLE
FROM grouped_data
GROUP BY CaseID, NAME, first_address, first_zip;

3. SQL Server #

用ROW_NUMBER()筛选每组第一行，再关联原表聚合ROLE：

WITH ranked_data AS (
    SELECT 
        CaseID, NAME, ADDRESS, ZIP, ROLE,
        ROW_NUMBER() OVER (PARTITION BY CaseID, NAME ORDER BY (SELECT NULL)) AS rn
    FROM your_table_name
),
first_rows AS (
    SELECT CaseID, NAME, ADDRESS, ZIP
    FROM ranked_data
    WHERE rn = 1
)
SELECT 
    fr.CaseID, fr.NAME, fr.ADDRESS, fr.ZIP,
    STRING_AGG(rd.ROLE, ', ') AS ROLE
FROM first_rows fr
JOIN ranked_data rd ON fr.CaseID = rd.CaseID AND fr.NAME = rd.NAME
GROUP BY fr.CaseID, fr.NAME, fr.ADDRESS, fr.ZIP;

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