使用listagg()within group() 函数帮助网友解决一个问题

Oracle 11g SQL聚合技巧

最新推荐文章于 2025-08-22 16:48:27 发布

转载最新推荐文章于 2025-08-22 16:48:27 发布 · 513 阅读

文章标签：

#数据库

本文介绍了一个具体的Oracle 11g SQL聚合问题及其解决方案。通过使用listagg() withingroup()函数，实现了对特定字段的数据进行组合，解决了实际业务场景中常见的数据汇总需求。

今天一网友在朋友发布一道悬赏题，

要实现如下内容：

对方悬赏10块钱，求一条SQL，咱先不说钱多少的事。这样的问题在SQL开发当中经常遇到，在oracle 10g 和11g 实现方法不一样。
11g 采用 listagg() within group ()来实现：

with tmp0 as
(select '1' as IDS, '2' as IDS2 from dual
union all
select '1' as IDS, '3' as IDS2 from dual
union all
select '1' as IDS, '4' as IDS2 from dual
union all
select '2' as IDS, '2' as IDS2
from dual
union all
select '2' as IDS, '5' as IDS2
from dual
union all
select '2' as IDS, '8' as IDS2
from dual
union all
select '3' as IDS, '2' as IDS2
from dual
union all
select '3' as IDS, '3' as IDS2
from dual
union all
select '3' as IDS, '4' as IDS2
from dual
union all
select '4' as IDS, '9' as IDS2
from dual
union all
select '8' as IDS, '9' as IDS2
from dual),
b as
(select IDS, listagg(IDS2, ',') within group(order by IDS) as ids2
from tmp0
group by IDS)

select b.*
from b, b a
where b.ids2 = a.ids2
and b.ids <> a.ids
order by b.ids

结果图接不贴了，ITPUB的插入图片做的确实不咋滴!这和那啥啥第一网站不太相符啊，呵呵！

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29507357/viewspace-2124768/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29507357/viewspace-2124768/