如何在SQL中基于公共字段合并重复记录并聚合非空字段？

原始表数据 #

原查询的问题 #

你原查询的GROUP BY子句错误地包含了HOME_PHONE_NO和WORK_PHONE_NO，这会让数据库把不同电话值的记录当成独立分组，完全无法实现合并相同键值记录的需求。

正确的SQL查询 #

只需要将GROUP BY的字段限定为你要合并的唯一键：CONT, ID, EFFECTIVE_DATE, M_ID，然后对每个电话字段使用MAX()聚合函数——MAX()会自动忽略NULL值，取对应字段的非空有效数据：

SELECT 
    CONT, 
    ID, 
    EFFECTIVE_DATE, 
    M_ID,
    MAX(HOME_PHONE_NO) AS HOME_PHONE_NO, 
    MAX(WORK_PHONE_NO) AS WORK_PHONE_NO,  
    MAX(PREFERED_CONTACT_NO) AS PREFERED_CONTACT_NO 
FROM  
    Wrk_INSERT1
GROUP BY 
    CONT, ID, EFFECTIVE_DATE, M_ID

执行结果 #

执行后会得到合并后的单条记录：

补充说明 #

如果你的SQL方言支持窗口函数（比如SQL Server、PostgreSQL），也可以用以下方式实现（不过对于当前场景，MAX()的聚合写法更简洁高效）：

SELECT DISTINCT
    CONT,
    ID,
    EFFECTIVE_DATE,
    M_ID,
    COALESCE(FIRST_VALUE(HOME_PHONE_NO) OVER (PARTITION BY CONT, ID, EFFECTIVE_DATE, M_ID ORDER BY CASE WHEN HOME_PHONE_NO IS NOT NULL THEN 0 ELSE 1 END), '') AS HOME_PHONE_NO,
    COALESCE(FIRST_VALUE(WORK_PHONE_NO) OVER (PARTITION BY CONT, ID, EFFECTIVE_DATE, M_ID ORDER BY CASE WHEN WORK_PHONE_NO IS NOT NULL THEN 0 ELSE 1 END), '') AS WORK_PHONE_NO,
    COALESCE(FIRST_VALUE(PREFERED_CONTACT_NO) OVER (PARTITION BY CONT, ID, EFFECTIVE_DATE, M_ID ORDER BY CASE WHEN PREFERED_CONTACT_NO IS NOT NULL THEN 0 ELSE 1 END), '') AS PREFERED_CONTACT_NO
FROM Wrk_INSERT1

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