Oracle 中的 LISTAGG 返回不同的值

我正在尝试在 Oracle 中使用 LISTAGG 函数.我只想获得该列的不同值.有没有一种方法可以在不创建函数或过程的情况下只获取不同的值?

<前>col1 col2 Created_by1 2 史密斯1 2 约翰1 3 阿杰1 4 公羊1 5 杰克

我需要选择col1和col2的LISTAGG(不考虑第3列).当我这样做时，LISTAGG 的结果是这样的:[2,2,3,4,5]

我需要在这里删除重复的2"；我只需要 col2 和 col1 的不同值.

解决方案

19c 及更高版本:

select listagg(distinct the_column, ',') 组内(按 the_column 排序)来自the_table

18c 及更早版本:

在组内选择 listagg(the_column, ',') (按 the_column 排序)从 (选择不同的 the_column来自the_table) t

如果您需要更多列，您可能正在寻找这样的内容:

select col1, listagg(col2, ',') 组内(按col2排序)从 (选择 col1，col2，row_number() over (partition by col1, col2 order by col1) as rn来自 foo按 col1,col2 排序)其中 rn = 1按 col1 分组；

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 

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]

I need to remove the duplicate '2' here; I need only the distinct values of col2 against col1.

解决方案

19c and later:

select listagg(distinct the_column, ',') within group (order by the_column)
from the_table

18c and earlier:

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;