SqlServer stuff + for xml path 用法(以权限角色管理为例讲解)

本例中用到的场景是希望把每个用户的角色以及权限都合并成一条进行展示
//单纯角色
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