PL/SQL多字符串及对应值拼接：查询结果聚合改写需求

问题描述 #

现有PL/SQL查询返回的数据集中，同一AcctNbr、MIACCTTYPCD、BAL对应多条USRFLDCD与VALUE记录，需将同一分组下的USRFLDCD和VALUE分别以逗号拼接合并，得到聚合后的目标结果。

当前查询结果 #

期望目标结果 #

原查询语句 #

select a.acctnbr, a.MIACCTTYPCD, userfieldcd.USRFLDCD, userfieldcd.value, m.bal as odbal
from table a
left join table h on a.acctnbr = h.acctnbr and h.entity = 'REOD' and h.inactv is null and h.acctentityvalue = 'Y'
left join table b on a.acctnbr = b.acctnbr and b.lastmaint = to_date('2022-08-25','YYYY-MM-DD')
left join table m on a.acctnbr = m.acctnbr and baltypecode = 'MOVD',
(select u.acctnbr, u.USRFLDCD, u.value 
from table u
where u.USRFLDCD in ('PSMX', 'PSOD') 
) USERFIELDCD 
where a.ACCTCD = 'CK'
and a.curracctstatcd = 'ACT'
and m.bal <> 0
and USERFIELDCD.acctnbr = a.acctnbr
order by a.acctnbr;

修改后的查询语句 #

SELECT 
    t.acctnbr,
    t.MIACCTTYPCD,
    LISTAGG(t.USRFLDCD, ', ') WITHIN GROUP (ORDER BY t.USRFLDCD) AS USRFLDCD,
    LISTAGG(t.VALUE, ', ') WITHIN GROUP (ORDER BY t.USRFLDCD) AS VALUE,
    t.odbal AS BAL
FROM (
    -- 原查询逻辑作为子查询
    SELECT 
        a.acctnbr, 
        a.MIACCTTYPCD, 
        userfieldcd.USRFLDCD, 
        userfieldcd.value, 
        m.bal AS odbal
    FROM table a
    LEFT JOIN table h ON a.acctnbr = h.acctnbr AND h.entity = 'REOD' AND h.inactv IS NULL AND h.acctentityvalue = 'Y'
    LEFT JOIN table b ON a.acctnbr = b.acctnbr AND b.lastmaint = TO_DATE('2022-08-25','YYYY-MM-DD')
    LEFT JOIN table m ON a.acctnbr = m.acctnbr AND baltypecode = 'MOVD'
    JOIN (
        SELECT u.acctnbr, u.USRFLDCD, u.value 
        FROM table u
        WHERE u.USRFLDCD IN ('PSMX', 'PSOD') 
    ) USERFIELDCD ON USERFIELDCD.acctnbr = a.acctnbr
    WHERE a.ACCTCD = 'CK'
      AND a.curracctstatcd = 'ACT'
      AND m.bal <> 0
) t
GROUP BY t.acctnbr, t.MIACCTTYPCD, t.odbal
ORDER BY t.acctnbr;

说明 #

1. 使用Oracle的LISTAGG函数实现字符串拼接，WITHIN GROUP (ORDER BY t.USRFLDCD)确保拼接顺序与原数据一致，可根据需求调整排序字段；
2. 将原查询包装为子查询，外层按AcctNbr、MIACCTTYPCD、BAL（对应子查询中的odbal）分组聚合；
3. 原查询中的逗号连接表写法已改为显式JOIN语法，提升代码可读性。

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