SQL 查询根据另一列的值查找唯一列值

Question

我有一个像这样的 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)

如您所见，如果我想查看权限 ID 40 的所有 RoleID，我可以通过此查询轻松获取。

select * from DemoTable where PrivilageID =  40

这给了我结果 101, 102, 104, 107

但我的要求恰恰相反。我知道这一特定组合 101, 102, 104, 107 只有一个 PrivilageID 。我需要一个可以传递 101, 102, 104, 107 的查询，我将得到 40

的输出

我尝试的是:我创建了这样的相交查询:

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

这可行，但还有更好的方法吗？

Answer 1

如果我理解正确，您可以尝试将 HAVING 与条件聚合函数 COUNT 结合使用 DISTINCT 条件。

我们只需在 CASE WHEN 表达式中添加过滤器 RoleId，计数可能等于您的 RoleId 数字。

SELECT PrivilageId 
FROM DemoTable 
GROUP BY PrivilageId 
HAVING COUNT(DISTINCT CASE WHEN RoleId IN (101, 102, 104, 107) THEN RoleId END) = 4

sqlfiddle