求权限查询的 SQL 语句。

[zzmsl]

表结构如下，求 SQL 语句，查询所有角色拥有的权限！
如：
User Shop Select
Admin Shop Select
Admin Shop Update

Role 角色表
RoleID RoleName
1 User
2 Admin

PrivilegeInRole 角色与权限对应表
RoleID PrivilegeID
1 1
2 2
2 2

Privilege 权限表
PrivilegeID ResourceID OperationID
1 1 1
2 1 1
2 1 2

Resource 资源表
ResourceID Description
1 Shop

Operation 操作表
OperationID Description
1 Select
2 Update

[-狙击手-]

select g.RoleName,f.Description1,f.Description2
from (
select e.RoleID ,d.*
from Role e
left join (
select PrivilegeID c ,a.Description as Description1 ,b.Description as Description2
from Privilege
left join Resource a on c.ResourceID = a.ResourceID
left join Operation b on c.OperationID = b.OperationID )d on e.PrivilegeID= d.PrivilegeID) f
left join Role g on g.RoleID = f.RoleID

[zzmsl]

列前缀 'c' 与查询中所用的表名或别名不匹配!

[云中客]

把C去掉

[$扫地僧$]

select R.RoleName,T.R_Description,T.O_Description
from PrivilegeInRole P,
Role R,
(
select P.PrivilegeID,R.Description as R_Description,O.Description as O_Description
from Privilege P,
Resource R,
Operation O
Where P.ResourceID = R.ResourceID
and P.OperationID=O.OperationID
)T
where P.RoleID=R.RoleID
and T.PrivilegeID=R.PrivilegeID

[zzmsl]

老大。还是不行啊！

[fa_ge]

角色表
create table Role

(RoleID smallint, RoleName varchar(10))
insert into Role
select 1, 'User' union
select 2, 'Admin'

角色与权限对应表
create table PrivilegeInRole
(RoleID smallint, PrivilegeID smallint)
insert into PrivilegeInRole
select 1, 1 union all
select 2, 2 union all
select 2, 2



权限表
create table Privilege

(PrivilegeID smallint, ResourceID smallint, OperationID smallint)
insert into Privilege
select 1, 1, 1 union all
select 2, 1, 1 union all
select 2, 1, 2
资源表
create table Resource

(ResourceID smallint, Description varchar(10))
insert into Resource
select 1, 'Shop'

操作表

create table Operation
(OperationID smallint,Description varchar(10))
insert into Operation
select 1, 'Select' union all
select 2, 'Update'



select distinct * from
(
select a.*,b.PrivilegeID from Role a left join PrivilegeInRole b on a.RoleID=b.RoleID
)a
left join
(
select b.Description,c.* from Operation b
left join
(
select a.Description as Description1,b.* from Resource a left join Privilege b on a.ResourceID=b.ResourceID
)c on b.OperationID=c.OperationID
)d on a.PrivilegeID=d.PrivilegeID


RoleID RoleName PrivilegeID Description Description1 PrivilegeID ResourceID OperationID
------ ---------- ----------- ----------- ------------ ----------- ----------
1 User 1 Select Shop 1 1 1
2 Admin 2 Select Shop 2 1 1
2 Admin 2 Update Shop 2 1 2

（所影响的行数为 3 行）