SqlServer stuff + for xml path 用法(以权限角色管理为例讲解)
来源:互联网 发布:js实现图片轮播解析 编辑:程序博客网 时间:2024/06/03 18:59
本例中用到的场景是希望把每个用户的角色以及权限都合并成一条进行展示
//单纯角色
select U.nameuname,D.name dname,R.name rname from [User] U,Department D,Role R,PowerP,UserToRole UR,RoleToPower RP
where 1=1 and U.departmentId=D.id andU.id=UR.userId and R.id=UR.roleId and R.id=RP.roleId and P.id=RP.powerId groupby U.name,D.name,R.name
//单纯权限
select U.nameuname,D.name dname,P.name pname from [User] U,Department D,Role R,PowerP,UserToRole UR,RoleToPower RP
where 1=1 and U.departmentId=D.id andU.id=UR.userId and R.id=UR.roleId and R.id=RP.roleId and P.id=RP.powerId groupby U.name,D.name,P.name
//合并角色
selectt1.usn,t1.userid,t1.uname,t1.dname,ability=STUFF((SELECT ','+t.rnameFROM(select U.name uname,D.name dname,R.name rname from [User] U,DepartmentD,Role R,Power P,UserToRole UR,RoleToPower RP
where 1=1 and U.departmentId=D.id andU.id=UR.userId and R.id=UR.roleId and R.id=RP.roleId and P.id=RP.powerId groupby U.name,D.name,R.name)t
WHERE uname=t1.uname FOR XML PATH('')), 1, 1, '')
FROM (select U.sn usn,U.id userid,U.nameuname,R.name rname,D.name dname from [User] U,Department D,Role R,PowerP,UserToRole UR,RoleToPower RP
where 1=1 and U.departmentId=D.id andU.id=UR.userId and R.id=UR.roleId and R.id=RP.roleId and P.id=RP.powerId groupby U.name,R.name,D.name,U.sn,U.id)t1
group by t1.uname,t1.dname,t1.usn,t1.userid
//合并权限
selectt1.usn,t1.userid,t1.uname,t1.dname,ability=STUFF((SELECT ','+t.pnameFROM(select U.name uname,D.name dname,P.name pname from [User] U,DepartmentD,Role R,Power P,UserToRole UR,RoleToPower RP
where 1=1 and U.departmentId=D.id andU.id=UR.userId and R.id=UR.roleId and R.id=RP.roleId and P.id=RP.powerId groupby U.name,D.name,P.name)t
WHERE uname=t1.uname FOR XML PATH('')), 1, 1, '')
FROM (select U.sn usn,U.id userid,U.nameuname,D.name dname,P.name pname from [User] U,Department D,Role R,PowerP,UserToRole UR,RoleToPower RP
where 1=1 and U.departmentId=D.id andU.id=UR.userId and R.id=UR.roleId and R.id=RP.roleId and P.id=RP.powerId groupby U.name,P.name,D.name,U.sn,U.id)t1
group by t1.uname,t1.dname,t1.usn,t1.userid
//将权限和角色做关联当做临时表再进行权限角色合并
selectt1.usn,t1.userid,t1.uname,t1.dname,ability=STUFF((SELECT ','+t.pnameFROM(select U.name uname,D.name dname,P.name pname from [User] U,DepartmentD,Role R,Power P,UserToRole UR,RoleToPower RP
where 1=1 and U.departmentId=D.id andU.id=UR.userId and R.id=UR.roleId and R.id=RP.roleId and P.id=RP.powerId groupby U.name,D.name,P.name)t
WHERE uname=t1.uname FOR XML PATH('')), 1, 1, '')
FROM (select U.sn usn,U.id userid,U.nameuname,D.name dname,P.name pname from [User] U,Department D,Role R,PowerP,UserToRole UR,RoleToPower RP
where 1=1 and U.departmentId=D.id andU.id=UR.userId and R.id=UR.roleId and R.id=RP.roleId and P.id=RP.powerId groupby U.name,P.name,D.name,U.sn,U.id)t1
group by t1.uname,t1.dname,t1.usn,t1.userid
union
selectt1.usn,t1.userid,t1.uname,t1.dname,ability=STUFF((SELECT ','+t.rnameFROM(select U.name uname,D.name dname,R.name rname from [User] U,DepartmentD,Role R,Power P,UserToRole UR,RoleToPower RP
where 1=1 and U.departmentId=D.id andU.id=UR.userId and R.id=UR.roleId and R.id=RP.roleId and P.id=RP.powerId groupby U.name,D.name,R.name)t
WHERE uname=t1.uname FOR XML PATH('')), 1, 1, '')
FROM (select U.sn usn,U.id userid,U.nameuname,R.name rname,D.name dname from [User] U,Department D,Role R,PowerP,UserToRole UR,RoleToPower RP
where 1=1 and U.departmentId=D.id andU.id=UR.userId and R.id=UR.roleId and R.id=RP.roleId and P.id=RP.powerId groupby U.name,R.name,D.name,U.sn,U.id)t1
group by t1.uname,t1.dname,t1.usn,t1.userid
//权限角色合并
selectT1.usn,T1.userid,T1.uname,T1.dname,PowerOrRole=STUFF((SELECT ','+T.ability FROM
(selectt1.usn,t1.userid,t1.uname,t1.dname,ability=STUFF((SELECT ','+t.pnameFROM(select U.name uname,D.name dname,P.name pname from [User] U,DepartmentD,Role R,Power P,UserToRole UR,RoleToPower RP
where 1=1 and U.departmentId=D.id andU.id=UR.userId and R.id=UR.roleId and R.id=RP.roleId and P.id=RP.powerId groupby U.name,D.name,P.name)t
WHERE uname=t1.uname FOR XML PATH('')), 1, 1, '')
FROM (select U.sn usn,U.id userid,U.nameuname,D.name dname,P.name pname from [User] U,Department D,Role R,PowerP,UserToRole UR,RoleToPower RP
where 1=1 and U.departmentId=D.id andU.id=UR.userId and R.id=UR.roleId and R.id=RP.roleId and P.id=RP.powerId groupby U.name,P.name,D.name,U.sn,U.id)t1
group by t1.uname,t1.dname,t1.usn,t1.userid
union
selectt1.usn,t1.userid,t1.uname,t1.dname,ability=STUFF((SELECT ','+t.rnameFROM(select U.name uname,D.name dname,R.name rname from [User] U,DepartmentD,Role R,Power P,UserToRole UR,RoleToPower RP
where 1=1 and U.departmentId=D.id andU.id=UR.userId and R.id=UR.roleId and R.id=RP.roleId and P.id=RP.powerId groupby U.name,D.name,R.name)t
WHERE uname=t1.uname FOR XML PATH('')), 1, 1, '')
FROM (select U.sn usn,U.id userid,U.nameuname,R.name rname,D.name dname from [User] U,Department D,Role R,PowerP,UserToRole UR,RoleToPower RP
where 1=1 and U.departmentId=D.id andU.id=UR.userId and R.id=UR.roleId and R.id=RP.roleId and P.id=RP.powerId groupby U.name,R.name,D.name,U.sn,U.id)t1
group by t1.uname,t1.dname,t1.usn,t1.userid)T
WHERE uname=T1.uname FOR XML PATH('')), 1, 1, '')
FROM (selectt1.usn,t1.userid,t1.uname,t1.dname,ability=STUFF((SELECT ','+t.pnameFROM(select U.name uname,D.name dname,P.name pname from [User] U,DepartmentD,Role R,Power P,UserToRole UR,RoleToPower RP
where 1=1 and U.departmentId=D.id andU.id=UR.userId and R.id=UR.roleId and R.id=RP.roleId and P.id=RP.powerId groupby U.name,D.name,P.name)t
WHERE uname=t1.uname FOR XML PATH('')), 1, 1, '')
FROM (select U.sn usn,U.id userid,U.nameuname,D.name dname,P.name pname from [User] U,Department D,Role R,PowerP,UserToRole UR,RoleToPower RP
where 1=1 and U.departmentId=D.id andU.id=UR.userId and R.id=UR.roleId and R.id=RP.roleId and P.id=RP.powerId groupby U.name,P.name,D.name,U.sn,U.id)t1
group by t1.uname,t1.dname,t1.usn,t1.userid
union
selectt1.usn,t1.userid,t1.uname,t1.dname,ability=STUFF((SELECT ','+t.rnameFROM(select U.name uname,D.name dname,R.name rname from [User] U,DepartmentD,Role R,Power P,UserToRole UR,RoleToPower RP
where 1=1 and U.departmentId=D.id andU.id=UR.userId and R.id=UR.roleId and R.id=RP.roleId and P.id=RP.powerId groupby U.name,D.name,R.name)t
WHERE uname=t1.uname FOR XML PATH('')), 1, 1, '')
FROM (select U.sn usn,U.id userid,U.nameuname,R.name rname,D.name dname from [User] U,Department D,Role R,PowerP,UserToRole UR,RoleToPower RP
where 1=1 and U.departmentId=D.id andU.id=UR.userId and R.id=UR.roleId and R.id=RP.roleId and P.id=RP.powerId groupby U.name,R.name,D.name,U.sn,U.id)t1
group byt1.uname,t1.dname,t1.usn,t1.userid)T1
group by T1.uname,T1.dname,T1.usn,T1.userid
阅读全文
0 0
- SqlServer stuff + for xml path 用法(以权限角色管理为例讲解)
- 以sqlserver处理父子级关系查询为列介绍with as、cast()、for xml path()、stuff()的使用
- 【sqlserver】字符串拼接实现(for xml path ,stuff)
- sqlserver for xml path用法
- stuff 与 for xml path('')
- sqlserver中 多条数据合并成一条数据 (stuff 与 for xml path 连用)
- sqlserver 用 STUFF 和 for xml path 把查询结果连接成字符串
- SQL之 Stuff和For xml path
- Sql Server STUFF与FOR XML PATH
- sqlserver for xml path
- SQLSERVER FOR XML PATH
- 灵活运用 SQL SERVER FOR XML PATH和SQL Stuff用法
- SQLServer 角色与权限管理
- SqlServer中STUFF用法
- stuff(select ',' + fieldname from tablename for xml path('')),1,1,'')
- sql的for xml path 和 stuff 结合使用
- SQLServer新增用户,角色设置,权限管理
- sqlserver数据库代码为角色分配权限
- 历经 33 天,终于拿到了心仪的 Offer
- 高效工作
- struts2的工作原理
- 离散时间LTI系统
- Python2.7字符编码详解
- SqlServer stuff + for xml path 用法(以权限角色管理为例讲解)
- Meta http-equiv属性详解(转)
- aspnetcore.dll文件缺失【模块 DLL C:\Program Files (x86)\IIS Express\aspnetcore.dll 未能加载。返回的数据为错误信息。】
- Postgresql的数据迁移
- 在机器学习中如何应对不均衡分类问题?
- hibernate 二级缓存
- 创建一个git仓库
- iOS学习笔记--封装倒计时按钮
- 字节流转换成字符串