编写存储过程合并含重复行的多表，生成唯一bpm_no结果表

我来帮你搞定这个需求！咱们的核心目标是把T2里同一个bpm_no的用户合并成逗号分隔的字符串，把T3里同一个bpm_no的欠款金额求和，再和主表T1关联，得到每个唯一bpm_no对应的最终结果。下面分不同数据库给出具体的存储过程实现：

1. MySQL 版本存储过程 #

DELIMITER //

CREATE PROCEDURE GenerateFinalTable()
BEGIN
    -- 直接返回结果集，若需插入到固定结果表，可替换为INSERT语句
    SELECT 
        t1.bpm_no,
        t2_agg.user_list,
        FORMAT(t3_agg.total_loan_os, 2) AS loan_os
    FROM T1
    INNER JOIN (
        -- 聚合T2的user为逗号分隔列表
        SELECT 
            bpm_no,
            GROUP_CONCAT(`user` SEPARATOR ',') AS user_list
        FROM T2
        GROUP BY bpm_no
    ) t2_agg ON t1.bpm_no = t2_agg.bpm_no
    INNER JOIN (
        -- 先去掉金额中的逗号转数值，再求和
        SELECT 
            bpm_no,
            SUM(CAST(REPLACE(loan_os, ',', '') AS DECIMAL(10,2))) AS total_loan_os
        FROM T3
        GROUP BY bpm_no
    ) t3_agg ON t1.bpm_no = t3_agg.bpm_no;
    
    -- 插入到结果表的写法（取消注释即可使用）：
    -- INSERT INTO FinalTable (bpm_no, user, loan_os)
    -- SELECT ...;
END //

DELIMITER ;

2. SQL Server 版本存储过程 #

CREATE PROCEDURE GenerateFinalTable
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT 
        t1.bpm_no,
        t2_agg.user_list,
        FORMAT(t3_agg.total_loan_os, 'N2') AS loan_os
    FROM T1
    INNER JOIN (
        -- 聚合T2的user为逗号分隔列表
        SELECT 
            bpm_no,
            STRING_AGG(`user`, ',') AS user_list
        FROM T2
        GROUP BY bpm_no
    ) t2_agg ON t1.bpm_no = t2_agg.bpm_no
    INNER JOIN (
        -- 处理金额格式后求和
        SELECT 
            bpm_no,
            SUM(CAST(REPLACE(loan_os, ',', '') AS DECIMAL(10,2))) AS total_loan_os
        FROM T3
        GROUP BY bpm_no
    ) t3_agg ON t1.bpm_no = t3_agg.bpm_no;
    
    -- 插入到结果表的写法（取消注释即可使用）：
    -- INSERT INTO FinalTable (bpm_no, user, loan_os)
    -- SELECT ...;
END

3. Oracle 版本存储过程 #

CREATE OR REPLACE PROCEDURE GenerateFinalTable
IS
    CURSOR final_cursor IS
        SELECT 
            t1.bpm_no,
            t2_agg.user_list,
            TO_CHAR(t3_agg.total_loan_os, 'FM99999999.00') AS loan_os
        FROM T1
        INNER JOIN (
            -- 聚合T2的user为逗号分隔列表
            SELECT 
                bpm_no,
                LISTAGG(`user`, ',') WITHIN GROUP (ORDER BY `user`) AS user_list
            FROM T2
            GROUP BY bpm_no
        ) t2_agg ON t1.bpm_no = t2_agg.bpm_no
        INNER JOIN (
            -- 处理金额格式后求和
            SELECT 
                bpm_no,
                SUM(TO_NUMBER(REPLACE(loan_os, ',', ''))) AS total_loan_os
            FROM T3
            GROUP BY bpm_no
        ) t3_agg ON t1.bpm_no = t3_agg.bpm_no;
BEGIN
    -- 输出结果到控制台，若需插入到结果表可替换为INSERT语句
    FOR rec IN final_cursor LOOP
        DBMS_OUTPUT.PUT_LINE('bpm_no: ' || rec.bpm_no || ', user: ' || rec.user_list || ', loan_os: ' || rec.loan_os);
    END LOOP;
    
    -- 插入到结果表的写法（取消注释即可使用）：
    -- INSERT INTO FinalTable (bpm_no, user, loan_os)
    -- SELECT * FROM final_cursor;
END;
/

关键细节说明： #

• 字符串聚合差异：不同数据库的字符串聚合函数不同，MySQL用GROUP_CONCAT，SQL Server用STRING_AGG，Oracle用LISTAGG，按需选择即可。
• 金额处理：示例中loan_os带千分位逗号，必须先通过REPLACE清除逗号再转数值，否则会出现转换错误。
• 连接逻辑：当前用INNER JOIN，仅保留同时存在于三个表的bpm_no；若需保留T1所有记录（哪怕T2/T3无对应数据），可改为LEFT JOIN。
• 灵活性：存储过程支持直接返回结果集，也可将结果插入到指定的最终表，两种写法已在代码中用注释标注。

内容的提问来源于stack exchange，提问作者Md Kamran Azam