Oracle中的LISTAGG返回不同的值 [英] LISTAGG in Oracle to return distinct values
问题描述
我正在尝试在Oracle中使用LISTAGG
函数.我只想获取该列的不同值.有没有一种方法可以只获取不同的值而无需创建函数或过程?
I am trying to use the LISTAGG
function in Oracle. I would like to get only the distinct values for that column. Is there a way in which I can get only the distinct values without creating a function or a procedure?
col1 col2 Created_by
1 2 Smith
1 2 John
1 3 Ajay
1 4 Ram
1 5 Jack
我需要选择col1和col2的LISTAGG
(不考虑第3列).当我这样做时,我得到LISTAGG
的结果: [2,2,3,4,5]
I need to select col1 and the LISTAGG
of col2 (column 3 is not considered). When I do that, I get something like this as the result of LISTAGG
: [2,2,3,4,5]
我需要在此处删除重复的"2";我只需要col2对col1的不同值即可.
I need to remove the duplicate '2' here; I need only the distinct values of col2 against col1.
推荐答案
19c及更高版本:
select listagg(distinct the_column, ',') within group (order by the_column)
from the_table
18c及更早版本:
select listagg(the_column, ',') within group (order by the_column)
from (
select distinct the_column
from the_table
) t
如果您需要更多列,则可能是您所需要的:
If you need more columns, something like this might be what you are looking for:
select col1, listagg(col2, ',') within group (order by col2)
from (
select col1,
col2,
row_number() over (partition by col1, col2 order by col1) as rn
from foo
order by col1,col2
)
where rn = 1
group by col1;
这篇关于Oracle中的LISTAGG返回不同的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!