Oracle SQL：合并重复键值并求和拼接字符串

现有Oracle SQL查询通过LISTAGG生成拼接字符串，要求每个table1.id仅返回一行数据，但load_unit_qty列存在重复键值对，需要将相同键对应的数值求和并仅显示一次。

原查询语句 #

SELECT table1.id,
       RTRIM (
           REGEXP_REPLACE (
               LISTAGG (
                   CASE
                       WHEN table2.unit_id = '-1' THEN NULL
                       ELSE TO_CHAR (table2.unit_idunit_id)
                   END,
                   ',')
               WITHIN GROUP (ORDER BY table2.unit_idunit_id),
               '([^,]+)(,|\1|)+',
               '\1|'),
           '|')
           AS source_load_unit_id,
       RTRIM (
           REGEXP_REPLACE (
               LISTAGG (
                   CASE
                       WHEN table2.unit_lp = 'NONE' THEN NULL
                       ELSE TO_CHAR (table2.unit_lp)
                   END,
                   ',')
               WITHIN GROUP (ORDER BY table2.unit_lp),
               '([^,]+)(,|\1|)+',
               '\1|'),
           '|')
           AS source_load_unit_number,
       RTRIM (
           REGEXP_REPLACE (
               LISTAGG (
                   CASE
                       WHEN table2.target_unit_id = '-1' THEN NULL
                       ELSE TO_CHAR (table2.target_unit_id)
                   END,
                   ',')
               WITHIN GROUP (ORDER BY
                                 table2.target_unit_id,
                                 '([^,]+)(,|\1|)+',
                                 '\1|'),
               '|'))
           AS target_load_unit_id,
       RTRIM (
           REGEXP_REPLACE (
               LISTAGG (
                   CASE
                       WHEN table2.target_unit_lp = 'NONE'
                       THEN
                           NULL
                       ELSE
                           TO_CHAR (table2.target_unit_lp)
                   END,
                   ',')
               WITHIN GROUP (ORDER BY table2.target_unit_lp),
               '([^,]+)(,|\1|)+',
               '\1|'),
           '|')
           AS target_load_unit_number,
       LISTAGG (
           CASE
               WHEN table2.target_unit_id LIKE '-1'
               THEN
                   NULL
               ELSE
                      table2.target_unit_id
                   || '='
                   || TO_CHAR (table2.QUANTITY - table2.shortpick)
           END,
           '|')
       WITHIN GROUP (ORDER BY table2.target_unit_id)
           AS load_unit_qty,
       REPLACE (
           TRIM (
               BOTH '|' FROM
                   REGEXP_REPLACE (
                       LISTAGG ('|' || table2.user_name || '|',
                                NULL)
                       WITHIN GROUP (ORDER BY table2.user_name),
                       '(\|[^|]+\|)(\1)+',
                       '\1')),
           '||',
           '|')
           AS pick_user
FROM my_table1 table1
     JOIN mytable2 table2 ON table2.test1_id = table1.id
GROUP BY table1.id

当前输出（load_unit_qty列） #

|load_unit_qty          |
|-----------------------|
|Test1=1|Test2=1|Test1=1|
|Test3=2                |
|Test4=4|Test4=2|Test5=3|

期望输出（load_unit_qty列） #

|load_unit_qty          |
|-----------------------|
|Test1=2|Test2=1        |
|Test3=2                |
|Test4=6|Test5=3        |

解决方案 #

核心思路是先对每个table1.id和target_unit_id分组求和，得到每个键对应的总数量，再基于这个中间结果使用LISTAGG拼接成目标格式，同时修正原查询中target_load_unit_id列的语法错误（ORDER BY子句混入正则参数）。

修改后的查询语句：

WITH aggregated_data AS (
    SELECT 
        table1.id,
        table2.target_unit_id,
        -- 按target_unit_id求和
        SUM(table2.QUANTITY - table2.shortpick) AS total_qty,
        -- 提前聚合其他列的原始数据
        LISTAGG(
            CASE WHEN table2.unit_id = '-1' THEN NULL ELSE TO_CHAR(table2.unit_id) END, ','
        ) WITHIN GROUP (ORDER BY table2.unit_id) AS source_unit_ids,
        LISTAGG(
            CASE WHEN table2.unit_lp = 'NONE' THEN NULL ELSE TO_CHAR(table2.unit_lp) END, ','
        ) WITHIN GROUP (ORDER BY table2.unit_lp) AS source_unit_lps,
        LISTAGG(
            CASE WHEN table2.target_unit_id = '-1' THEN NULL ELSE TO_CHAR(table2.target_unit_id) END, ','
        ) WITHIN GROUP (ORDER BY table2.target_unit_id) AS target_unit_ids,
        LISTAGG(
            CASE WHEN table2.target_unit_lp = 'NONE' THEN NULL ELSE TO_CHAR(table2.target_unit_lp) END, ','
        ) WITHIN GROUP (ORDER BY table2.target_unit_lp) AS target_unit_lps,
        LISTAGG('|' || table2.user_name || '|', NULL) WITHIN GROUP (ORDER BY table2.user_name) AS raw_user_names
    FROM my_table1 table1
    JOIN mytable2 table2 ON table2.test1_id = table1.id
    GROUP BY table1.id, table2.target_unit_id
),
final_aggregated AS (
    SELECT 
        id,
        -- 处理source_load_unit_id去重拼接
        RTRIM(REGEXP_REPLACE(source_unit_ids, '([^,]+)(,\1)+', '\1|'), '|') AS source_load_unit_id,
        -- 处理source_load_unit_number去重拼接
        RTRIM(REGEXP_REPLACE(source_unit_lps, '([^,]+)(,\1)+', '\1|'), '|') AS source_load_unit_number,
        -- 处理target_load_unit_id去重拼接（修正原语法错误）
        RTRIM(REGEXP_REPLACE(target_unit_ids, '([^,]+)(,\1)+', '\1|'), '|') AS target_load_unit_id,
        -- 处理target_load_unit_number去重拼接
        RTRIM(REGEXP_REPLACE(target_unit_lps, '([^,]+)(,\1)+', '\1|'), '|') AS target_load_unit_number,
        -- 拼接已求和的键值对
        LISTAGG(
            CASE WHEN target_unit_id LIKE '-1' THEN NULL ELSE target_unit_id || '=' || TO_CHAR(total_qty) END, '|'
        ) WITHIN GROUP (ORDER BY target_unit_id) AS load_unit_qty,
        -- 处理pick_user去重格式化
        REPLACE(TRIM(BOTH '|' FROM REGEXP_REPLACE(raw_user_names, '(\|[^|]+\|)(\1)+', '\1')), '||', '|') AS pick_user
    FROM aggregated_data
    GROUP BY id, source_unit_ids, source_unit_lps, target_unit_ids, target_unit_lps, raw_user_names
)
SELECT * FROM final_aggregated;

关键说明： #

1. CTE aggregated_data：先按table1.id和target_unit_id分组，计算每个目标单元的总数量，同时提前聚合其他列所需的原始拼接数据，避免重复计算。
2. CTE final_aggregated：对每个table1.id再次聚合，将求和后的键值对拼接成load_unit_qty，同时对其他列应用原有的去重和格式化逻辑。
3. 语法修正：移除原target_load_unit_id的WITHIN GROUP (ORDER BY)子句中错误混入的正则表达式参数，保证查询语法合法。

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