各位 现在有两张表
username deptID
a 1
b 1
c 1
d 2
e 2deptID deptName
1 部门1
1 部门2有没有办法一条sql语句显示成deptid userName deptName
1 a,b,c 部门1
2 d,e 部门2
求调教啊 先道个谢
username deptID
a 1
b 1
c 1
d 2
e 2deptID deptName
1 部门1
1 部门2有没有办法一条sql语句显示成deptid userName deptName
1 a,b,c 部门1
2 d,e 部门2
求调教啊 先道个谢
group by a.deptid,b.deptname;
WITH temp1 AS(
select 'a' as username,1 as deptid from dual union all
select 'b',1 from dual union all
select 'c',1 from dual union all
select 'd',2 from dual union all
select 'e',2 from dual
),
temp2 AS(
select 1 as deptid,'部门1' as deptname from dual union all
select 2 ,'部门2' from dual
)
select p.deptid,
listagg(t.username,',') within group (order by t.username) as username,
p.deptname
from temp1 t,temp2 p
where t.deptid=p.deptid
group by p.deptid,p.deptname
order by p.deptid-------------------------
deptid userName deptName
1 a,b,c 部门1
2 d,e 部门2方法二:用wm_concat()select p.deptid,
wm_concat(t.username) as username,
p.deptname
from temp1 t,temp2 p
where t.deptid=p.deptid
group by p.deptid,p.deptname
order by p.deptid方法三:用CONNECT BY select deptid,
ltrim(max(sys_connect_by_path(username,',')),','),
deptname
from (
select p.deptid,t.username,p.deptname,
row_number()over(partition by t.deptid order by t.username) as ar
from temp1 t,temp2 p
where t.deptid=p.deptid
)
start with ar=1
connect by prior ar=ar-1
and username=prior username
group by deptid,deptname
order by deptid