如何规范化含NULL值的MySQL多表关联生成的非标准化表？

我有一个通过PrimaryId关联三个不同表得到的非标准化表，多数字段具备有效值，但部分标识符字段存在NULL值。原表数据如下：

PrimaryId, Name, Internal_System_One_Identifier, Internal_System_Two_Identifier
abc123, 'Joes Supermarket', '22e0b9f6', NULL
abc123, 'Joes Supermarket', NULL, '22e0b9f8'
abc124, 'Suzannas Coffee', '22e0ba6c', NULL
abc124, 'Suzannas Coffee', '22e0ba6c', '22e0ba6e'
abc125, 'Rogers Aquatics', NULL, '22e0ba6c'
abc125, 'Rogers Aquatics', '22e0ba6e', '22e0ba6c'
abc126, 'Frankies Records', NULL, NULL
abc127, 'Henrys Hammers', '22e0bb7d','22e0bb7e'

希望将其规范化为如下结构（注：abc123的目标标识符示例疑似笔误，以下方案以提取分组内非NULL有效值为准）：

PrimaryId, Name, Internal_System_One_Identifier, Internal_System_Two_Identifier
abc123, 'Joe''s Supermarket', '22e0b9f6', '22e0b9f8'
abc124, 'Suzannas Coffee', '22e0ba6c', '22e0ba6e'
abc125, 'Rogers Aquatics', '22e0ba6e', '22e0ba6c'
abc126, 'Frankies Records', NULL, NULL
abc127, 'Henrys Hammers', '22e0bb7d','22e0bb7e'

我认为应该使用COALESCE函数或窗口函数，但不清楚具体实现方法。

解决方案 #

方法1：COALESCE结合聚合函数 #

利用COALESCE配合MAX()/MIN()聚合函数，按PrimaryId分组，提取每个分组内的非NULL标识符值。该方法适用于同一PrimaryId下，同一标识符字段仅存在一个非NULL有效值的场景：

SELECT
    PrimaryId,
    MAX(Name) AS Name, -- 同一PrimaryId的Name值一致，MAX/MIN均可
    MAX(Internal_System_One_Identifier) AS Internal_System_One_Identifier,
    MAX(Internal_System_Two_Identifier) AS Internal_System_Two_Identifier
FROM your_table_name
GROUP BY PrimaryId;

方法2：窗口函数（适配复杂场景） #

如果需要处理更特殊的业务规则（比如优先取某来源的有效值），可以用FIRST_VALUE()窗口函数，按PrimaryId分区后过滤NULL值取优先有效值：

WITH ranked_data AS (
    SELECT
        PrimaryId,
        Name,
        Internal_System_One_Identifier,
        Internal_System_Two_Identifier,
        -- 为非NULL的System One标识符设置优先排序
        ROW_NUMBER() OVER (
            PARTITION BY PrimaryId
            ORDER BY CASE WHEN Internal_System_One_Identifier IS NOT NULL THEN 0 ELSE 1 END
        ) AS rn_one,
        -- 为非NULL的System Two标识符设置优先排序
        ROW_NUMBER() OVER (
            PARTITION BY PrimaryId
            ORDER BY CASE WHEN Internal_System_Two_Identifier IS NOT NULL THEN 0 ELSE 1 END
        ) AS rn_two
    FROM your_table_name
)
SELECT DISTINCT
    PrimaryId,
    Name,
    FIRST_VALUE(Internal_System_One_Identifier) OVER (
        PARTITION BY PrimaryId ORDER BY rn_one
    ) AS Internal_System_One_Identifier,
    FIRST_VALUE(Internal_System_Two_Identifier) OVER (
        PARTITION BY PrimaryId ORDER BY rn_two
    ) AS Internal_System_Two_Identifier
FROM ranked_data;

补充说明 #

• 两种方法均可实现按PrimaryId合并记录、填充NULL值的需求。
• 若同一PrimaryId下的同一标识符字段存在多个不同非NULL值，需先明确业务规则（如取最新值、优先特定来源），再调整聚合或排序逻辑。

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