Oracle SQL-从listagg检索不同的数据 [英] Oracle Sql - retrieve distinct data from a listagg

问题描述

使用此命令，我可以检索按department_id

With this command I'm able to retrieve a count of all the distinct manager_id grouped by department_id

select department_id, count( distinct manager_id) 
from employees
group by department_id

如果我想查看这些经理的列表，可以使用它，但是问题是他们是重复的，而且并没有区别

If I want to see a list of those managers, I can use this but the problem is that they are repeated and not distinct

select 
    department_id,
    listagg(manager_id, ' | ') within group(order by manager_id)
from  
    employees
group by 
    department_id;

这会输出一长串重复的manager_id列表.

This outputs a long list of repeated manager_id.

单行示例:

100 | 100 | 100 | 100 | 100 | 120 | 120 | 120 | 120 | 120 | 120 | 120 | 120 | 121 | 121 | 121 | 121 | 121 | 121 | 121 | 121 | 122 | 122 | 122 | 122 | 122 | 122 | 122 | 122 | 123 | 123 | 123 | 123 | 123 | 123 | 123 | 123 | 124 | 124 | 124 | 124 | 124 | 124 | 124 | 124

我希望能够有一个唯一的manager_id列表，而不是像上面那样重复.我该怎么办?

I want to be able to have a list of unique manager_ids, not repeating like the above one. How should I do this ?

推荐答案

您可以使用子查询删除重复项:

You can use a subquery to remove duplicates:

select department_id, count(*),
       listagg(manager_id, ' | ') within group (order by manager_id)
from (select distinct department_id, manager_id
      from employees
     ) e
group by department_id

这篇关于Oracle SQL-从listagg检索不同的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！