oracle DISTINCT提示需要group by

Question

单纯DISTINCT一个字段有没有出现这样的问题。还是我语法用错了

Answer 1

你这个语法有问题啊，你使用的distinct是对后面所有的字段进行去重，我这里跟你提供一种方式，你可以尝试一下

先去重，再聚合
select t.department_name depname,
t.department_key,
listagg(t.class_key, ',') within group(order by t.class_key) as class_keys
from (select distinct s.class_key, s.department_key, s.department_name
from V_YDXG_TEACHER_KNSRDGL s) t
group by t.department_key, t.department_name

--或者
select s.department_key,
s.department_name,
listagg(s.class_key, ',') within group(order by s.class_key) as class_keys
from (select t.department_key,
t.department_name,
t.class_key,
row_number() over(partition by t.department_key, t.department_name, t.class_key order by t.department_key, t.department_name) as rn
from V_YDXG_TEACHER_KNSRDGL t
order by t.department_key, t.department_name, t.class_key) s
where rn = 1
group by s.department_key, s.department_name;