如何获取匹配另一张表所有外键的表行数据？

现有临时表结构及数据 #

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所有roleId值的记录。简单来说，就是保留那些每个#tempRole里的roleId都对应存在的appId（示例中appId=26同时对应roleId 1和2，而27只对应1，因此仅返回appId=26的相关行）。#tempRole是其他查询的输出，行数不固定。

错误尝试及原因 #

此前使用以下语句未得到预期结果：

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

该语句逻辑是查找roleId等于#tempRole中所有roleId的行，但一个roleId不可能同时等于多个不同值（比如同时等于1和2），因此返回空结果，不符合需求。

正确SQL查询方法 #

方法1：分组统计匹配角色数

通过分组统计每个appId匹配的不同roleId数量，与#tempRole的总角色数对比，筛选出数量相等的appId：

select tra.*
from #tempRoleApp tra
inner join (
    select appId
    from #tempRoleApp
    where roleId in (select roleId from #tempRole)
    group by appId
    having count(distinct roleId) = (select count(*) from #tempRole)
) valid_apps on tra.appId = valid_apps.appId

方法2：NOT EXISTS反查验证

通过双层NOT EXISTS，检查当前appId是否不存在未匹配的角色：

select tra.*
from #tempRoleApp tra
where not exists (
    select 1
    from #tempRole tr
    where not exists (
        select 1
        from #tempRoleApp tra2
        where tra2.roleId = tr.roleId and tra2.appId = tra.appId
    )
)

方法3：窗口函数统计匹配数

利用窗口函数给每个appId计算匹配的角色数，再与总角色数对比筛选：

select roleId, appId
from (
    select 
        tra.*,
        count(distinct tra.roleId) over (partition by tra.appId) as matched_role_count,
        (select count(*) from #tempRole) as total_role_count
    from #tempRoleApp tra
    where tra.roleId in (select roleId from #tempRole)
) t
where matched_role_count = total_role_count

内容的提问来源于stack exchange，提问作者Samuel