如何通过SQL分组合并行以减少行数且不丢失数据？

情况1：同主键下各列仅一个非NULL值 #

如果你的数据中，同一个primary_key对应的每一列最多只有一个非NULL值，直接用GROUP BY搭配MAX()（或MIN()）即可，聚合函数会自动忽略NULL，保留有效数据，且合并为单行：

SELECT 
    primary_key,
    MAX(column_1) AS column_1,
    MAX(column_2) AS column_2,
    MAX(column_3) AS column_3,
    MAX(column_4) AS column_4
FROM (
    SELECT 9999 as primary_key ,1 AS column_1, 2 as column_2, NULL  as column_3, NULL as column_4 FROM DUAL
    UNION ALL
    SELECT 9999, NULL, NULL, 3, 4 FROM DUAL
    UNION ALL
    SELECT 9999, 5, 6, NULL, NULL FROM DUAL
) t
GROUP BY primary_key;

返回结果：

注意：如果同主键下某一列有多个非NULL值（比如示例里的column_1有1和5），MAX()只会取最大值，会丢失数据，这种情况用下面的方法。

情况2：同主键下某列有多个非NULL值（需保留全部） #

要保留所有非NULL数据，用字符串拼接函数，以Oracle为例（对应你用的DUAL表），使用LISTAGG()：

SELECT 
    primary_key,
    LISTAGG(column_1, ', ') WITHIN GROUP (ORDER BY column_1) AS column_1,
    LISTAGG(column_2, ', ') WITHIN GROUP (ORDER BY column_2) AS column_2,
    LISTAGG(column_3, ', ') WITHIN GROUP (ORDER BY column_3) AS column_3,
    LISTAGG(column_4, ', ') WITHIN GROUP (ORDER BY column_4) AS column_4
FROM (
    SELECT 9999 as primary_key ,1 AS column_1, 2 as column_2, NULL  as column_3, NULL as column_4 FROM DUAL
    UNION ALL
    SELECT 9999, NULL, NULL, 3, 4 FROM DUAL
    UNION ALL
    SELECT 9999, 5, 6, NULL, NULL FROM DUAL
) t
GROUP BY primary_key;

返回结果：

如果要把结果中的NULL替换为空字符串提升可读性，搭配NVL()函数：

SELECT 
    primary_key,
    NVL(LISTAGG(column_1, ', ') WITHIN GROUP (ORDER BY column_1), '') AS column_1,
    NVL(LISTAGG(column_2, ', ') WITHIN GROUP (ORDER BY column_2), '') AS column_2,
    NVL(LISTAGG(column_3, ', ') WITHIN GROUP (ORDER BY column_3), '') AS column_3,
    NVL(LISTAGG(column_4, ', ') WITHIN GROUP (ORDER BY column_4), '') AS column_4
FROM (
    SELECT 9999 as primary_key ,1 AS column_1, 2 as column_2, NULL  as column_3, NULL as column_4 FROM DUAL
    UNION ALL
    SELECT 9999, NULL, NULL, 3, 4 FROM DUAL
    UNION ALL
    SELECT 9999, 5, 6, NULL, NULL FROM DUAL
) t
GROUP BY primary_key;

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