Oracle 表操作之合并多行成一行（ listagg within group 、wmsys.wm_concat）

最新推荐文章于 2025-03-27 13:33:35 发布

程序猿雷子

最新推荐文章于 2025-03-27 13:33:35 发布

阅读量1.6k

点赞数

CC 4.0 BY-SA版权

分类专栏： Database 文章标签： oracle

本文链接：https://blog.youkuaiyun.com/lwladzhj/article/details/79995680

Database 专栏收录该内容

6 篇文章

订阅专栏

本文介绍了在Oracle数据库中如何使用listagg within group 和 wmsys.wm_concat 函数将多行数据合并成一行。通过示例查询展示了在不同场景下，如何根据条件对多个字段进行聚合，实现数据的汇总展示。这两个方法适用于11g及以上版本的Oracle数据库。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、查询语法: select column1,listagg(column2,',') within group (orderby column3) as column4... from tableName1

注意：oracle11g以上才支持此查法

1.listagg例子：

select senc_orgid ,senc_orgname,securname,securid ,depusecurname,depusecurid ,commebname,commebid, securlevname, depusecurlevname from (

select orgT.senc_orgid ,orgT.senc_orgname,count(senc_orgid) brannum from (
select t1.id senc_orgid,t1.org_name senc_orgname,t3.id thrd_orgid , t3.thrd_orgname from t_Pb_Organization t1 ,( select t2.id,t2.org_name thrd_orgname ,t2.pid from t_Pb_Organization t2 ) t3 where (t1.id=t3.pid or (t1.id=t3.id and t1.pid=2) ) and t1.pid !=0 and t1.isdeleted='0' order by t3.id
) orgT group by senc_orgid,senc_orgname order by senc_orgid
) orgTT

     left join (
select e.org_id, t.leader_name securname ,t.inner_party securlevname ,t.pm_id securid from t_pb_org_leader t,t_pb_election_info e where e.id=t.election_id and t.leave_date is null and t.inner_party='书记' and t.isdeleted='0' and e.on_duty=1
        ) securT on securT.org_id=orgTT.senc_orgid   left join (
        select e.org_id ,'委员'commeblevname , listagg( t.leader_name,',') WITHIN GROUP (ORDER BY org_id) AS commebname,
         listagg( t.pm_id,',') WITHIN GROUP (ORDER BY org_id) AS commebid  from t_pb_org_leader t,t_pb_election_info e
        where e.id=t.election_id and t.leave_date is null and t.inner_party like '%委员%' and t.isdeleted='0' and e.on_duty=1 group by e.org_id
       ) commebT on commebT.org_id=orgTT.senc_orgid left join (
                select e.org_id, t.leader_name depusecurname ,t.inner_party depusecurlevname ,t.pm_id depusecurid from t_pb_org_leader t,t_pb_election_info e where e.id=t.election_id and t.leave_date is null and t.inner_party='副书记' and t.isdeleted='0' and e.on_duty=1
         ) depusecurT on depusecurT.org_id=orgTT.senc_orgid

2.wmsys.wm_concat例子(无需使用 order by 进行排序)：

select aa.orgid_3,aa.orgname_3,aa.orgnum_3,wmsys.wm_concat(age) age ,wmsys.wm_concat(age_num) age_num from (

select tt.*,count(1) age_num from (
    select org.orgid_3,org.orgname_3,org.orgnum_3,case when mem1.tim <=30 then 'age1'
    when (mem1.tim >30 and mem1.tim <=40 ) then 'age2' when (mem1.tim >40 and mem1.tim <=50 ) then 'age3'
    when (mem1.tim >50 and mem1.tim <=60 ) then 'age4' when (mem1.tim >60) then 'age5' end age from (
         select t1.id orgid_2,t1.org_name orgname_2,t1.org_num orgnum_2,t3.id orgid_3,t3.orgname_3 ,t3.orgnum_3 from
         t_Pb_Organization t1 ,( select t2.id,t2.org_name orgname_3 ,t2.pid,t2.org_num orgnum_3 from t_Pb_Organization t2 ) t3
         where

           /*(t1.id=t3.pid or (t1.id=t3.id and t1.pid=2) ) 二级党组织*/
              ( ( t1.id=11 and t3.id=11 ) or (t1.id=t3.pid and t3.pid=11) ) /*三级党组织*/
          and t1.pid !=0 and t1.isdeleted='0' order by orgnum_3
    ) org left join (
         select mem.id,mem.name, org_mem.org_id,mem.sex,mem.nation_id,
            (months_between(sysdate ,mem.birthday))/12 tim,to_char(mem.birthday,'yyyy-MM-dd HH24:mi'),mem.dgree1
            from t_pb_partymembers_infos mem
         left join t_pb_org_partymember org_mem on org_mem.pb_id=mem.id
         where mem.isdeleted='0' and org_mem.org_id is not null
    ) mem1 on mem1.org_id=org.orgid_3 where mem1.tim is not null order by mem1.tim
    ) tt group by orgid_3, orgname_3, orgnum_3,age order by orgnum_3 ,age
) aa group by aa.orgid_3,aa.orgname_3,aa.orgnum_3 order by aa.orgnum_3