角色权限表:RolePermission;字段有:ID,RoleID,PermissionID 最好还有个权限表。要是没有独立的权限表也可以这样查寻一个角色没有的权限: SELECT DISTINCT PermissionID FROM RolePermission AS A WHERE NOT EXISTS (SELECT 1 FROM RolePermission AS B WHERE B.RoleID=@RoleID AND B.PermissionID=A.PermissionID) --@RoleID是要找的角色
LZ看看这样可以用嘛。 select Role.Name, PermissionType.TypeName, Permission.[Name] from PermissionType inner join Permission on Permission.ID = PermissionType.PermissionTypeID left join RolePermission on RolePermission.PermissionID = Permission.ID left join Role on Role.ID = RolePermission.PermissionID where Role.ID = 'XXXXXX' and PermissionType.ID = 'XXXXX' and Permission.ID is null
修改一下我原来的语句就可以, try:SELECT DISTINCT PermissionID FROM RolePermission AS A INNER JOIN Role AS B ON B.[id]=A.RoleID AND PermissionTypeID=@PermissionTypeID WHERE NOT EXISTS (SELECT 1 FROM RolePermission AS B WHERE B.RoleID=@RoleID AND B.PermissionID=A.PermissionID)--@RoleID是要找的角色 --@PermissionTypeID 是要类型ID
不好意思,写错和漏了几个字母,应该这样: SELECT DISTINCT A.PermissionID FROM RolePermission AS A INNER JOIN Role AS B ON B.[id]=A.RoleID AND B.PermissionTypeID=@PermissionTypeID WHERE NOT EXISTS (SELECT 1 FROM RolePermission AS C WHERE C.RoleID=@RoleID AND C.PermissionID=A.PermissionID) --@RoleID是要找的角色 --@PermissionTypeID 是要类型ID
--查已有权限 select t1.* from ( select r.id as RoleID,t.id as PermissionTypeID from Role r cross join PermissionType t ) as t1 left join ( select r.id as RoleID,t.id as PermissionTypeID from Role r inner join RolePermission rp on r.id=rp.RoleID inner join Permission p on rp.PermissionID=p.ID inner join PermissionType t on p.PermissionTypeID=t.ID ) as t2 on t1.RoleID=t2.RoleID and t1.PermissionTypeID=t2.PermissionTypeID where t2.RoleID is not null--查没有的权限 select t1.* from ( select r.id as RoleID,t.id as PermissionTypeID from Role r cross join PermissionType t ) as t1 left join ( select r.id as RoleID,t.id as PermissionTypeID from Role r inner join RolePermission rp on r.id=rp.RoleID inner join Permission p on rp.PermissionID=p.ID inner join PermissionType t on p.PermissionTypeID=t.ID ) as t2 on t1.RoleID=t2.RoleID and t1.PermissionTypeID=t2.PermissionTypeID where t2.RoleID is null
看了下回帖,谢谢各位了,我问了我同学解决了这段时间就没来,说下试过对的sql: select * from (select P.*, RP.RoleID from (select P.PermissionID, P.PermissionNAME, PT.PermissionTypeName from Permission P, PermissionType PT where P.PermissionTypeID = PT.PermissionTypeID and PT.PermissionTypeID = @PermissionTypeID) P left join (select * from RolePermission where RoleID = @RoleID) RP on RP.PermissionID = P.PermissionID) T where RoleID is null
角色权限表:RolePermission;字段有:ID,RoleID,PermissionID
最好还有个权限表。要是没有独立的权限表也可以这样查寻一个角色没有的权限:
SELECT DISTINCT PermissionID
FROM RolePermission AS A
WHERE NOT EXISTS (SELECT 1 FROM RolePermission AS B WHERE B.RoleID=@RoleID AND B.PermissionID=A.PermissionID)
--@RoleID是要找的角色
表间关系是这样的:角色与权限是多对多的,权限类别与权限是一对多的,每个权限类别里有很多权限,每个用户都有可能拥有这些权限,我想查的就是这个权限类别里的多个权限中被某个角色拥有的 和 未拥有的,拥有的和未拥有的加起来就是某个权限类别里的所有权限,拥有的用复合查询就可以了,想请教某个权限类别里某个角色未拥有的权限怎么查
谢谢啊,我要查的是某个权限类别里的权限,你这样都没用到权限类别
select
Role.Name,
PermissionType.TypeName,
Permission.[Name]
from
PermissionType
inner join Permission on Permission.ID = PermissionType.PermissionTypeID
left join RolePermission on RolePermission.PermissionID = Permission.ID
left join Role on Role.ID = RolePermission.PermissionID
where
Role.ID = 'XXXXXX'
and
PermissionType.ID = 'XXXXX'
and
Permission.ID is null
try:SELECT DISTINCT PermissionID
FROM RolePermission AS A
INNER JOIN Role AS B ON B.[id]=A.RoleID AND PermissionTypeID=@PermissionTypeID
WHERE NOT EXISTS (SELECT 1 FROM RolePermission AS B WHERE B.RoleID=@RoleID AND B.PermissionID=A.PermissionID)--@RoleID是要找的角色
--@PermissionTypeID 是要类型ID
SELECT DISTINCT A.PermissionID
FROM RolePermission AS A
INNER JOIN Role AS B ON B.[id]=A.RoleID AND B.PermissionTypeID=@PermissionTypeID
WHERE NOT EXISTS (SELECT 1 FROM RolePermission AS C WHERE C.RoleID=@RoleID AND C.PermissionID=A.PermissionID)
--@RoleID是要找的角色
--@PermissionTypeID 是要类型ID
select t1.*
from (
select r.id as RoleID,t.id as PermissionTypeID
from Role r
cross join PermissionType t
) as t1
left join (
select r.id as RoleID,t.id as PermissionTypeID
from Role r
inner join RolePermission rp
on r.id=rp.RoleID
inner join Permission p
on rp.PermissionID=p.ID
inner join PermissionType t
on p.PermissionTypeID=t.ID
) as t2
on t1.RoleID=t2.RoleID and t1.PermissionTypeID=t2.PermissionTypeID
where t2.RoleID is not null--查没有的权限
select t1.*
from (
select r.id as RoleID,t.id as PermissionTypeID
from Role r
cross join PermissionType t
) as t1
left join (
select r.id as RoleID,t.id as PermissionTypeID
from Role r
inner join RolePermission rp
on r.id=rp.RoleID
inner join Permission p
on rp.PermissionID=p.ID
inner join PermissionType t
on p.PermissionTypeID=t.ID
) as t2
on t1.RoleID=t2.RoleID and t1.PermissionTypeID=t2.PermissionTypeID
where t2.RoleID is null
select *
from (select P.*, RP.RoleID
from (select P.PermissionID, P.PermissionNAME, PT.PermissionTypeName
from Permission P, PermissionType PT
where P.PermissionTypeID = PT.PermissionTypeID and PT.PermissionTypeID = @PermissionTypeID) P
left join (select * from RolePermission where RoleID = @RoleID) RP
on RP.PermissionID = P.PermissionID) T
where RoleID is null