查找在两个以上项目中共同工作的员工人数

Question

现在我们有三个表，分别是员工表、工作表和项目表。

对于员工表(带有样本数据(

性别M

employeeid	姓名
100	John
101	Jim	M
102	Sam	F
103	Quinn	F
400	简	F
401	玛丽	F

您需要像这样加入workson

select e1.employeeid, e2.employeeid, count(e1.projectid) as worked_together
from workson as e1
join workson as e2 on e1.projectid = e2.projectid and e1.employeeid < e2.employeeid
group by e1.employeeid, e2.employeeid
order by worked_together desc

CCD_ 2条件确保不包括诸如(Jim, Jim, Evil)之类的行和诸如(Jim, Sam, Evil) <-> (Sam, Jim, Evil)之类的相对对。

结果包含成对的员工和他们一起工作的项目数。添加having子句并不重要。

<1><1><1><1><1>

employeeid	一起工作
101	102	3
101	401	2
102	401	2
101	103
102	103
101	104
102	104
103	104

您需要将表workson与自身连接：

with u as 
(select w1.employeeid e1, w2.employeeid e2, count(*) ct
from workson w1 inner join workson w2
on w1.projectid = w2.projectid
and w1.employeeid < w2.employeeid
group by w1.employeeid, w2.employeeid
having count(*) > 2),
v as
(select distinct e1 from u union select distinct e2 from u)
select count(*) from v;

Fiddle