11个同PK表生成非规范化表：避免笛卡尔积的存储过程实现指引

嘿，这个问题我之前帮人处理过类似的，核心就是先规避单表内多记录带来的笛卡尔积问题，咱们分几种场景给你拆解方案：

这是最适合需要保留每条PK对应明细场景的方案——因为直接JOIN会把表A的4条和表B的6条拼成24条冗余数据，咱们先给每个表的同PK记录生成唯一行号，再用PK+行号做关联，确保每条记录一一对应。

举个SQL示例（以支持窗口函数的数据库为例，比如SQL Server、PostgreSQL、MySQL 8+）：

-- 先给每个表生成带行号的临时表
SELECT 
    PK,
    col1,
    col2,
    ROW_NUMBER() OVER (PARTITION BY PK ORDER BY create_time) AS rn -- 尽量用业务字段排序，别用SELECT NULL
INTO #temp_tableA
FROM tableA;

-- 其他10个表执行同样的逻辑，生成#temp_tableB到#temp_tableK

-- 最后按PK+行号关联所有临时表
SELECT 
    t1.PK,
    t1.col1, t1.col2,
    t2.col3, t2.col4,
    t3.col5, t3.col6,
    -- 依次补充其他表的字段
FROM #temp_tableA t1
LEFT JOIN #temp_tableB t2 ON t1.PK = t2.PK AND t1.rn = t2.rn
LEFT JOIN #temp_tableC t3 ON t1.PK = t3.PK AND t1.rn = t3.rn
-- 继续LEFT JOIN剩下的8个临时表

如果某个表的PK对应记录数更少，LEFT JOIN会自动补NULL，完全不会产生笛卡尔积。

如果不需要保留每条明细，只是要把同PK的所有数据合并成一行，那可以用聚合函数把多字段拼接成字符串/数组：

• SQL Server用STRING_AGG
• MySQL用GROUP_CONCAT
• PostgreSQL用STRING_AGG或ARRAY_AGG

示例代码：

-- 先聚合表A的多记录
SELECT 
    PK,
    STRING_AGG(col1, ', ') AS tableA_col1_list,
    STRING_AGG(col2, '; ') AS tableA_col2_list
INTO #temp_tableA_agg
FROM tableA
GROUP BY PK;

-- 其他表同理生成聚合临时表，然后直接按PK关联
SELECT 
    a.PK,
    a.tableA_col1_list, a.tableA_col2_list,
    b.tableB_col3_list, b.tableB_col4_list
FROM #temp_tableA_agg a
LEFT JOIN #temp_tableB_agg b ON a.PK = b.PK
-- 继续关联剩下的聚合表

这种方式下每个PK只有一行，彻底避免笛卡尔积，适合只需要汇总信息的场景。

因为表数量多，写静态SQL太繁琐，咱们可以写存储过程动态处理，比如先创建汇总表，再逐表插入/更新数据：

CREATE PROCEDURE sp_BuildDenormalizedSummary
AS
BEGIN
    -- 1. 创建临时汇总表，包含所有需要的字段
    CREATE TABLE #FinalSummary (
        PK INT,
        rn INT,
        -- 表A字段
        tableA_col1 VARCHAR(50),
        tableA_col2 INT,
        -- 表B字段
        tableB_col3 DATETIME,
        tableB_col4 DECIMAL(10,2),
        -- 依次添加其他9个表的字段
    );

    -- 2. 插入第一个表的带行号数据
    INSERT INTO #FinalSummary (PK, rn, tableA_col1, tableA_col2)
    SELECT 
        PK,
        ROW_NUMBER() OVER (PARTITION BY PK ORDER BY create_time) AS rn,
        col1, col2
    FROM tableA;

    -- 3. 逐表更新汇总表数据（以表B为例）
    WITH B_temp AS (
        SELECT 
            PK,
            col3, col4,
            ROW_NUMBER() OVER (PARTITION BY PK ORDER BY create_time) AS rn
        FROM tableB
    )
    UPDATE s
    SET 
        s.tableB_col3 = b.col3,
        s.tableB_col4 = b.col4
    FROM #FinalSummary s
    JOIN B_temp b ON s.PK = b.PK AND s.rn = b.rn;

    -- 4. 重复步骤3，依次更新剩下的9个表
    -- ... 这里省略其他表的更新逻辑

    -- 5. 输出或插入正式表
    SELECT * FROM #FinalSummary;
    -- INSERT INTO正式汇总表 SELECT * FROM #FinalSummary;

    DROP TABLE #FinalSummary;
END;

存储过程的好处是后续表结构变动时，只需要修改存储过程即可，不用重复写大量关联代码。

额外注意点 #

• 行号排序：一定要用业务上有意义的字段排序（比如创建时间、更新时间），别用ORDER BY (SELECT NULL)，否则不同数据库的默认排序可能导致行号对应错误。
• 性能优化：如果数据量很大，可以给临时表的PK+rn加复合索引，加快关联和更新速度。

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