不使用GROUP BY合并含NULL值的行：SQL数据压缩需求

问题背景 #

执行以下SQL查询：

SELECT nr, name, val_1, val_2, val_3 
FROM your_table

返回结果如下：

Nr. | Name       | Value 1 | Value 2 | Value 3
-----+------------+---------+---------+---------
   1 | Max        | 123     | NULL    | NULL 
   1 | Max        | NULL    | 456     | NULL 
   1 | Max        | NULL    | NULL    | 789
   9 | Lisa       | 1       | NULL    | NULL
   9 | Lisa       | 3       | NULL    | NULL
   9 | Lisa       | NULL    | NULL    | Hello
   9 | Lisa       | 9       | NULL    | NULL

期望将行压缩至最少，得到结果：

Nr. | Name       | Value 1 | Value 2 | Value 3
-----+------------+---------+---------+---------
   1 | Max        | 123     | 456     | 789
   9 | Lisa       | 1       | NULL    | Hello
   9 | Lisa       | 3       | NULL    | NULL
   9 | Lisa       | 9       | NULL    | NULL

对于Nr=1的Max，使用GROUP BY结合MAX函数即可实现合并，但Nr=9的Lisa需要将唯一的Value3非NULL行与第一个匹配Nr和Name且Value3为NULL的行合并，其余行保留，常规分组方法无法满足需求。

解决方案 #

使用CTE（公共表表达式）结合窗口函数和条件判断实现需求，以下是通用SQL实现（适配多数主流数据库，语法细节可根据数据库类型调整）：

WITH val3_non_null AS (
    -- 提取每个(nr,name)分组中val_3非NULL的值
    SELECT nr, name, val_3 
    FROM your_table 
    WHERE val_3 IS NOT NULL
),
ranked_null_val3_rows AS (
    -- 给val_3为NULL的行按分组编号，保留原始顺序
    SELECT
        nr,
        name,
        val_1,
        val_2,
        val_3,
        ROW_NUMBER() OVER (PARTITION BY nr, name ORDER BY (SELECT NULL)) AS row_num
    FROM your_table 
    WHERE val_3 IS NULL
),
full_merge_groups AS (
    -- 筛选可完全合并的分组：每个val列最多一个非NULL值
    SELECT 
        nr, 
        name, 
        MAX(val_1) AS val_1, 
        MAX(val_2) AS val_2, 
        MAX(val_3) AS val_3
    FROM your_table
    GROUP BY nr, name
    HAVING COUNT(DISTINCT CASE WHEN val_1 IS NOT NULL THEN val_1 END) <= 1
        AND COUNT(DISTINCT CASE WHEN val_2 IS NOT NULL THEN val_2 END) <= 1
        AND COUNT(DISTINCT CASE WHEN val_3 IS NOT NULL THEN val_3 END) <= 1
),
partial_merge_rows AS (
    -- 处理需部分合并的分组：将第一个val_3为NULL的行与val_3非NULL行合并
    SELECT
        r.nr,
        r.name,
        r.val_1,
        r.val_2,
        CASE WHEN r.row_num = 1 THEN v.val_3 ELSE r.val_3 END AS val_3
    FROM ranked_null_val3_rows r
    LEFT JOIN val3_non_null v ON r.nr = v.nr AND r.name = v.name
    WHERE NOT EXISTS (
        SELECT 1 FROM full_merge_groups m WHERE m.nr = r.nr AND m.name = r.name
    )
)
-- 合并两种结果并排序
SELECT * FROM full_merge_groups
UNION ALL
SELECT * FROM partial_merge_rows
ORDER BY nr, name, val_1;

逻辑说明 #

1. val3_non_null：提取所有val_3非NULL的记录，为后续合并提供值来源。
2. ranked_null_val3_rows：给val_3为NULL的行按(nr,name)分组编号，确保能定位到每组的第一行。
3. full_merge_groups：筛选出每个字段最多一个非NULL值的分组（如Max的记录），用MAX函数合并为单行。
4. partial_merge_rows：处理无法完全合并的分组（如Lisa的记录），将每组第一行的val_3替换为对应分组的非NULL值，其余行保持原样。
5. 最后合并两类结果并排序，得到目标输出。

数据库适配说明 #

• 若使用MySQL，ORDER BY (SELECT NULL)可能无法保留原始顺序，可替换为表中实际的排序字段（如主键或创建时间）。
• 部分数据库不支持COUNT(DISTINCT CASE...)，可调整为子查询统计非NULL值的数量。

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