查询参与完全相同关联项目的所有人员

AI 小北

2026-7-23

数据表

Project表

id	name	version
1	Swam	0.0.1
2	Dinali	0.0.1
3	Dinali	0.0.2
4	BigR	0.0.3
5	Kale	0.0.1
6	Kale	0.0.2

Person表

id	name
1	Jake
2	Skye
3	Kieth
4	Jim
5	Eliz
6	Haun

Person_Project表

id	person_id	project_id
1	1	1
2	2	1
3	2	2
4	3	1
5	3	2
6	4	1
7	4	4
8	5	1
9	6	1
10	6	2
11	6	3

查询需求

需要查询所有参与完全相同项目集合的人员。根据上述数据，结果应包含：

人员1（Jake）和5（Eliz）：两人均仅参与项目1
人员2（Skye）和3（Kieth）：两人均参与项目1和2

不应返回人员4（Jim）和6（Haun），因为没有其他人员与他们的项目参与集合完全一致。

解决方案

可以通过聚合人员的项目ID集合来判断是否完全相同，以下是实现SQL：

-- 生成每个人员的项目集合标识（排序后用逗号拼接）
WITH PersonProjectSets AS (
    SELECT
        person_id,
        STRING_AGG(CAST(project_id AS VARCHAR), ',' ORDER BY project_id) AS project_set
    FROM Person_Project
    GROUP BY person_id
)
-- 筛选出存在重复项目集合的人员
SELECT
    p.id,
    p.name,
    pps.project_set
FROM Person p
JOIN PersonProjectSets pps ON p.id = pps.person_id
WHERE pps.project_set IN (
    SELECT project_set
    FROM PersonProjectSets
    GROUP BY project_set
    HAVING COUNT(*) > 1
)
ORDER BY pps.project_set, p.id;