使用值对查找行并查找精确匹配的组

Question

我有这些表:

项目

<表类>id名称版本tbody><<tr>1皮特0.0.12赃物0.0.13赃物发布4赃物0.0.35甘蓝0.0.16甘蓝发布

您可以使用这个答案中描述的SQL关系除法逻辑。你感兴趣的是整除/无余数部分:

with project_list as (
select id
from project
where exists (
select *
from (values
('pete', '0.0.1'),
('swag', '0.0.1')
) as user_input(name, version)
where project.name = user_input.name and project.version = user_input.version
)
), person_project_copy as (
select person_id, case when project_list.id is not null then 1 end as is_required
from person_project
left join project_list on person_project.project_id = project_list.id
)
select person_id
from person_project_copy
group by person_id
having count(is_required) = (select count(*) from project_list)
and    count(*)           = (select count(*) from project_list)

DB<>

这是一个经典的关系除法无余问题。

首先将输入数据放入表变量或表值参数或临时表中。

那么您可以使用一个标准的关系除法答案

SELECT
p.name
FROM Person p
WHERE EXISTS (SELECT 1
FROM Person_Project pp
LEFT JOIN @input i
JOIN Project prj ON prj.name = i.name AND prj.version = i.version
ON pp.project_id = prj.id
WHERE pp.person_id = p.id
HAVING COUNT(prj.id) = COUNT(*)
AND COUNT(prj.id) = (SELECT COUNT(*) FROM @input)
);

,db&lt的在小提琴

它的作用如下:

• 取EXISTS子查询为true的所有Person行:
• 对于每个Person取其所有Person_Project行
• 左加入输入数据(同时加入匹配的Project)
• 将其分组并确保来自join的匹配数等于子查询中的行数…
• …并且该数字也等于输入行的总数。

还有其他的解决方案。