我有一个像这样的SQL Server表
此表的架构和用于生成此表的示例插入查询:
CREATE TABLE demotable (
Id int,
PrivilageId int,
RoleId int
);
insert into demotable values (1 ,40, 101)
insert into demotable values (2 ,12, 101)
insert into demotable values (3 ,40, 102)
insert into demotable values (4 ,40, 104)
insert into demotable values (5 ,12, 128)
insert into demotable values (6 ,40, 107)
insert into demotable values (7 ,17, 103)
insert into demotable values (8 ,17, 107)
insert into demotable values (9 ,15, 138)
insert into demotable values (10 ,15, 102)
insert into demotable values (11 ,17, 104)
insert into demotable values (12 ,12, 101)
正如你所看到的,如果我想查看Privilage ID 40的所有RoleIDs,我可以通过这个查询轻松地得到它.
select * from DemoTable where PrivilageID = 40
这给了我101, 102, 104, 107
的结果
但我的要求正好相反.我知道这个特殊的组合
只有一个PrivilageID
, 101, 102, 104, 107
, 104, 107PrivilageID
.我需要一个查询,我可以通过
,我会得到一个PrivilageID
, 101, 102, 104, 107
, 104, 10740
输出
我try 的是:我创建了如下相交查询:
select PrivilageId from DemoTable where RoleID = 101
intersect
select PrivilageId from DemoTable where RoleID = 102
intersect
select PrivilageId from DemoTable where RoleID = 104
intersect
select PrivilageId from DemoTable where RoleID = 107
这很管用,但还有更好的方法吗?