A表USERID NAME
1 张三B表
ID USERID VALUE
1 1 AA
2 1 BB
3 1 CC
4 1 DD
我想通过一条SQL语句得出一条记录USERID NAME VALUE
1 张三 AA;BB;CC;DD
1 张三B表
ID USERID VALUE
1 1 AA
2 1 BB
3 1 CC
4 1 DD
我想通过一条SQL语句得出一条记录USERID NAME VALUE
1 张三 AA;BB;CC;DD
where a.userid=b.userid
group by a.userid, a.name
left join b on a.userid=b.userid
group by a.userid,a.name
with a as
(select 1 c,'张三' d from dual),
b as
(
select 1 AA,'aa' BB from dual
union all
select 1,'bb' from dual
union all
select 1,'cc' from dual
union all
select 1,'dd' from dual
)
select c,d,BB
from
(
select AA,max(substr(sys_connect_by_path(BB,','),2)) BB
from
(select AA,BB,row_number()over(partition by AA order by AA) rn from b)
start with rn=1 connect by rn=rownum
group by AA
)BB,a where a.c=BB.AA
--result:1 1 张三 aa,bb,cc,dd