select u.userid,u,username,wm_concat(ri.roleid),wm_concat(ri.rolename) from userid u left join userrole ur on u.userid = ur.userid left join roleinfo ri on ri.roleid = ur.roleid group by u.userid,u,username
userid username roleid rolename 1100 小张 000001 总经理 1100 小张 000003 IT部在这个结果的基础上用wm_concat SELECT USERID,USERNAME,WM_CONCAT(ROLEID) FROM TABLE GROUP BY T.USERID,T.USERNAME
SELECT u.userid,u,username, LISTAGG(ri.roleid, ', ') WITHIN GROUP (ORDER BY ri.roleid) "roleid", LISTAGG(ri.rolename, ', ') WITHIN GROUP (ORDER BY ri.rolename) "rolename", FROM userid u left join userrole ur on u.userid = ur.userid left join roleinfo ri on ri.roleid = ur.roleid group by u.userid,u,username 还有这个也试试
wm_concat 报错:不是单组分组函数
你的数据库版本不支持这个函数, 仅供参考:、 SQL> select * from test1;
A B C ---------- ---------- ---------- 1 2 4 1 3 4 2 3 4 3 3 4 1 5 4 1 2 3 2 2 3SELECT A.A, ltrim(MAX(REPLACE(SYS_CONNECT_BY_PATH(B, '—'), '—', ',')),','), ltrim(MAX(REPLACE(SYS_CONNECT_BY_PATH(C, '—'), '—', ',')),',') FROM (SELECT T.*, ROW_NUMBER() OVER(PARTITION BY A ORDER BY B) AS RN FROM TEST1 T) A START WITH RN = 1 CONNECT BY PRIOR RN = RN - 1 AND PRIOR A = A GROUP BY A;结果: 1 2,2,3,5 4,3,4,4 2 2,3 3,4 3 3 4
left join userrole ur
on u.userid = ur.userid
left join roleinfo ri
on ri.roleid = ur.roleid
group by u.userid,u,username
1100 小张 000001 总经理
1100 小张 000003 IT部在这个结果的基础上用wm_concat
SELECT USERID,USERNAME,WM_CONCAT(ROLEID) FROM TABLE GROUP BY T.USERID,T.USERNAME
LISTAGG(ri.roleid, ', ') WITHIN GROUP (ORDER BY ri.roleid) "roleid",
LISTAGG(ri.rolename, ', ') WITHIN GROUP (ORDER BY ri.rolename) "rolename",
FROM userid u
left join userrole ur
on u.userid = ur.userid
left join roleinfo ri
on ri.roleid = ur.roleid
group by u.userid,u,username
还有这个也试试
wm_concat 报错:不是单组分组函数
仅供参考:、
SQL> select * from test1;
A B C
---------- ---------- ----------
1 2 4
1 3 4
2 3 4
3 3 4
1 5 4
1 2 3
2 2 3SELECT A.A,
ltrim(MAX(REPLACE(SYS_CONNECT_BY_PATH(B, '—'), '—', ',')),','),
ltrim(MAX(REPLACE(SYS_CONNECT_BY_PATH(C, '—'), '—', ',')),',')
FROM (SELECT T.*, ROW_NUMBER() OVER(PARTITION BY A ORDER BY B) AS RN
FROM TEST1 T) A
START WITH RN = 1
CONNECT BY PRIOR RN = RN - 1
AND PRIOR A = A
GROUP BY A;结果:
1 2,2,3,5 4,3,4,4
2 2,3 3,4
3 3 4