如何从与另一个表的所有外键匹配的表中获取行?

Question

假设我有两个表role和roleApp定义如下:

create table #tempRole(roleId int);
insert into #tempRole (roleId) values (1)
insert into #tempRole (roleId) values (2)
create table #tempRoleApp(roleId int, appId int);
insert into #tempRoleApp (roleId, appId) values (1, 26)
insert into #tempRoleApp (roleId, appId) values (2, 26)
insert into #tempRoleApp (roleId, appId) values (1, 27)

所以，从#tempRoleApp表中，我只想得到与#tempRole表(1和2)的所有值匹配的行，所以在这种情况下，输出需要是26(因为它匹配1和2)，但不是27，因为表没有2,27)。

#tempRole表实际上是另一个查询的输出，所以它可以有任意数量的值。

我尝试了一些事情，如:

select *
from #tempRoleApp
where roleId = ALL(select roleId FROM #tempRole)

它没有给任何东西…我又试了几样东西，但都没有得到我想要的。

Answer 1

我相信这就是你想要的。

select tra.appId
from #tempRoleApp as tra
join #tempRole as tr on tra.roleId = tr.roleId
group by tra.appId
having count(distinct tra.roleId) = (select count(distinct roleId) from #tempRole)

它使用count distinct获取tempRole表中的唯一roleId的总数，并在确认表之间的roleId匹配后，将其与每个appId的唯一计数进行比较。

正如您在评论中澄清的那样，一旦添加了另一个tempRole roleId，现在没有条目具有所有的id，因此没有返回任何行。