使用成对值的查找行并找到确切的匹配组

Question

我有这些表：

项目

ID	名称	版本
1	pete	0.0.1
2	赃物	0.0.1
3	赃物	0.0.2
4	赃物	0.0.3
5	羽衣甘蓝	0.0.1
6	羽衣甘蓝	0.0.2

人

id	名称
1	Jake
2	Skye
3	Kieth
4	Jim
5 Eliz 5	Eliz

Person_Project ID

Person_Id	Project_id	1
1	1	1
1	3	2
3	2 2 1	2
4	5	1
3 1 5 3	3	3 3
6	4	1
7	4 4 4 4	4
8 5	5	1
9	5	2
10	5	5

人将拥有独特的项目，这意味着没有两个人会在同一项目上工作。

我正在编写一个基于Java的API，其中我收到一个带有不同项目的JSON请求，我必须退还正在从事请求确切项目的人。

请求：

[
    {"name": "Pete", "version": "0.0.1"}
]

这应该返回Jake

请求：

[
    {"name": "Pete", "version": "0.0.1"},
    {"name": "Swag", "version": "0.0.1"}
]

这应该返回Skye

请求：

[
    {"name": "Pete", "version": "0.0.1"},
    {"name": "Swag", "version": "0.0.2"}
]

这应该返回kieth

我正在为此编写SQL，而没有得到我的东西需要。

这就是我所能做到的，

SELECT pe.id, pe.name
FROM person pe
LEFT JOIN person_project pepr on pepr.person_id = pe.id
WHERE pe.id IN (
    SELECT pepr.person_id
    FROM project pr
    LEFT JOIN person_project pepr ON pepr.project_id = pr.id
    WHERE pr.name IN ('Pete', 'Swag') AND pr.version IN ('0.0.1', '0.0.2')
    GROUP BY pepr.project_id
    HAVING COUNT(pepr.project_id) = 2
)
GROUP BY pe.id, pe.name
HAVING COUNT(pe.id) = 2

这是不正确的，因为我在用于其他项目的版本中使用。

Answer 1

您可以如此答案。您对说明确切的分区/无剩余部分感兴趣：

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)

Answer 2

这是一个经典关系划分无剩余的问题问题。

首先将输入数据放置在表变量或表值参数或temp中桌子。

然后，您可以使用标准关系划分答案之一

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)
);

这要做的就是以下内容：

• 取以下行，以下存在子查询为真：
• 对于每个person> person他们的所有person_project行
• 左 - 加入输入数据（同时加入匹配project）。
• 对其进行分组并确保连接的匹配次数等于子查询中的行数...
• ...数量也等于输入行的总数。

还有其他解决方案。