17,088
社区成员
发帖
与我相关
我的任务
分享
select userid, username, nvl(wmsys.wm_concat(classname), '没有选课')
from (select a.id userid, a.name username, b.name classname
from test1 a, test2 b, test3 c
where a.id = c.userid(+)
and b.id(+) = c.classid)
group by userid, username;
SQL> select userid,username,nvl(replace(wmsys.wm_concat(classname),',',' '), '没有选课')
2 from (select userid, username, classname, rownum rn
3 from (select distinct a.id userid, a.name username, b.name classname
4 from test1 a, test2 b, test3 c
5 where a.id = c.userid(+)
6 and b.id(+) = c.classid
7 )
8 )
9 group by userid, username;
USERID USERNAME CLASSNAME
-------------------- ---------------- --------------------------------------------------------------------------------
1 张三 数学 英语 语文
2 李四 数学 语文
3 王五 没有选课