有这样几个表：
角色表：Role；字段有：ID,Name
权限表：Permission；字段有：ID,Name，PermissionTypeID
权限类别表：PermissionType；字段有：ID，TypeName
角色权限表：RolePermission；字段有：ID,RoleID,PermissionID
我想查询这样的权限，某个角色在某个权限类别中 拥有的权限 和 还没有拥有的权限，输入的比较参数就是Role的ID和PermissionType的ID，拥有的权限好查些，但没拥有的权限不怎么好查，，我用not in实现了， 不过sql代码好长，不知道你们你怎么解决的
------解决方案--------------------
角色权限表：RolePermission；字段有：ID,RoleID,PermissionID
最好还有个权限表。要是没有独立的权限表也可以这样查寻一个角色没有的权限：
SELECT DISTINCT PermissionID
FROM RolePermission AS A
WHERE NOT EXISTS (SELECT 1 FROM RolePermission AS B WHERE [email protected] AND B.PermissionID=A.PermissionID)
[email protected]
------解决方案--------------------
修改一下我原来的语句就可以，
try:
SELECT DISTINCT PermissionID
FROM RolePermission AS A
INNER JOIN Role AS B ON B.[id]=A.RoleID AND [email protected]
WHERE NOT EXISTS (SELECT 1 FROM RolePermission AS B WHERE [email protected] AND B.PermissionID=A.PermissionID)
[email protected]
[email protected] 是要类型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