如何将Oracle的LISTAGG函数与独特的过滤器一起使用? [英] How to use Oracle's LISTAGG function with a unique filter?
本文介绍了如何将Oracle的LISTAGG函数与独特的过滤器一起使用?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这样的表:
group_id名称
-------- - -
1 David
1 John
1 Alan
1 David
2 Julie
2 Charles
我想得到以下结果:
group_id名称
-------- -----
1'Alan,David,John'
2'Charles,Julie'
我可以使用以下查询:
select group_id,
listagg(名称,',')在组内(按名称排序)作为名称
从可降级
group by group_id
得到这个(非常相似的结果):
group_id名称
-------- -----
1'Alan,David,David,John'
2'Charles,Julie'
任何想法如何通过
LISTAGG
调用中的唯一性过滤名称?解决方案我今天没有11g实例,但是你可以不使用:
SELECT group_id,
LISTAGG(name,',')WITHIN GROUP(ORDER BY name)AS名称
FROM(
SELECT UNIQUE
group_id,
名称
FROM可调节
)
GROUP BY group_id
I have a table like this:
group_id name -------- ---- 1 David 1 John 1 Alan 1 David 2 Julie 2 Charles
And I want the following result:
group_id names -------- ----- 1 'Alan, David, John' 2 'Charles, Julie'
I can use the following query:
select group_id, listagg(name, ',') within group (order by name) as names from demotable group by group_id
To get this (very similar result):
group_id names -------- ----- 1 'Alan, David, David, John' 2 'Charles, Julie'
Any ideas how I can filter the names by uniqueness in the
LISTAGG
call?解决方案I don't have an 11g instance available today but could you not use:
SELECT group_id, LISTAGG(name, ',') WITHIN GROUP (ORDER BY name) AS names FROM ( SELECT UNIQUE group_id, name FROM demotable ) GROUP BY group_id
这篇关于如何将Oracle的LISTAGG函数与独特的过滤器一起使用?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文