数据库视图查询去重复

[lilin0503]

Oracle数据库中三张表：
表1： UserInfo 用户基本信息
UserID UserName Gender MobleNumber
1100 小张 男 1388888888
1101 小王 男 1388888888
............................
表2： RoleInfo 角色信息表
RoleID RoleName Description
000001 总经理 总经理的角色
000002 销售 销售部的角色
000003 IT部 IT部的角色
..........................
表3： UserRole 用户角色关系表（多对多）
UserID RoleID
1100 000001
1100 000003
1101 000002
1101 000003
现有视图sql 语句
select distinct u.userid,u,username,ri.roleid,ri.rolename from userid u left join userrole ur on
u.userid = ur.userid left join roleinfo ri on ri.roleid = ur.roleid

查询结果类似如下：
userid username roleid rolename
1100 小张 000001 总经理
1100 小张 000003 IT部
.......................

也就是说我一个用户分配了多少个角色就有多少查询记录，能否通过改些视图实现如下查询结果
userid username roleid rolename
1100 小张 000001，000003 总经理，IT部
即查询结果每个人对应一条记录，对于分配的角色都合并起来，所有roleid合并到一个字段，所有rolename也合并到另一个字段，求各指点~~~~~~~~~~~~十万火急

[居然有人说长得比我帅]

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

[CT_LXL]

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

还有这个也试试

[lilin0503]

引用 楼主 lilin0503 的回复:

Oracle数据库中三张表： 表1： UserInfo 用户基本信息 UserID UserName Gender MobleNumber 1100 小张 男 1388888888 1101 小王 男 1388888888 ............................ 表2： RoleInfo 角色信息表 RoleID RoleName Description 000001 总经理 总经理的角色 000002 销售 销售部的角色 000003 IT部 IT部的角色 .......................... 表3： UserRole 用户角色关系表（多对多） UserID RoleID 1100 000001 1100 000003 1101 000002 1101 000003 现有视图sql 语句 select distinct u.userid,u,username,ri.roleid,ri.rolename from userid u left join userrole ur on u.userid = ur.userid left join roleinfo ri on ri.roleid = ur.roleid 查询结果类似如下： userid username roleid rolename 1100 小张 000001 总经理 1100 小张 000003 IT部 ....................... 也就是说我一个用户分配了多少个角色就有多少查询记录，能否通过改些视图实现如下查询结果 userid username roleid rolename 1100 小张 000001，000003 总经理，IT部 即查询结果每个人对应一条记录，对于分配的角色都合并起来，所有roleid合并到一个字段，所有rolename也合并到另一个字段，求各指点~~~~~~~~~~~~十万火急

wm_concat 报错：不是单组分组函数

[lilin0503]

引用 1 楼 gioh0022 的回复:

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

不好意思引用错了 是这个报错：不是单组分组函数

[CT_LXL]

你的数据库版本不支持这个函数， 仅供参考：、 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 3 SELECT 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

[lilin0503]

搞定了 谢谢给位