如何用LISTAGG聚合指定attribute的code，其余attribute单独展示

需求说明 #

针对table2表，需实现以下数据处理逻辑：

• 同一t_id下，attribute为a和b的code值通过LISTAGG函数聚合为字符串，对应attribute字段显示为'a,b'
• attribute为c和d的记录单独保留，不进行聚合

原始数据（table2） #

当前查询语句 #

select distinct t0.*, t3.pool,

listagg(distinct t1.code ,',) within group(order by t1.t_id)
over (partition by t1.t_id) as "CODES"

from table1 t0

left join table2 t1
on t0.id = t1.t_id

left join table3 t2
on t0.id = t2.t_id

left join table4 t3
on t0_id = t3.t_id

where t0.active = '1'
and t2.someother_id = '123456789'

修改后的查询语句 #

SELECT DISTINCT t0.*, t3.pool, t_processed.attribute, t_processed.code
FROM table1 t0
LEFT JOIN (
    -- 聚合a、b对应的code，统一attribute为'a,b'
    SELECT t_id, 'a,b' AS attribute, LISTAGG(code, ', ') WITHIN GROUP (ORDER BY code) AS code
    FROM table2
    WHERE attribute IN ('a', 'b')
    GROUP BY t_id
    -- 合并c、d的原始记录
    UNION ALL
    SELECT t_id, attribute, code
    FROM table2
    WHERE attribute IN ('c', 'd')
) t_processed ON t0.id = t_processed.t_id
LEFT JOIN table3 t2 ON t0.id = t2.t_id
LEFT JOIN table4 t3 ON t0.id = t3.t_id
WHERE t0.active = '1'
  AND t2.someother_id = '123456789'
ORDER BY t_processed.t_id, t_processed.attribute

逻辑说明 #

1. 子查询t_processed将table2数据分为两部分处理：
   • 对attribute为a/b的记录，按t_id分组，用LISTAGG聚合code，并将attribute统一设为'a,b'
   • 对attribute为c/d的记录，直接保留原始字段值
2. 将处理后的t_processed与原查询中的其他表关联，确保业务过滤条件（t0.active='1'、t2.someother_id='123456789'）生效
3. 最后通过ORDER BY确保结果按t_id和attribute排序，与期望输出一致

期望输出结果 #

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