时间戳

首页 > 解决方案 > 寻找永远在一起的员工

sql - 寻找永远在一起的员工

问题描述

我在公司数据库上有一项家庭作业，其关系如下：

Employee(eid,ename,salary,did,classification)  - primary key is eid
Project(pid,pname,did,budget,duedate)  - primary key is pid
Onproject(pid,eid,fdate) - primary key is (pid,eid)

还有更多的关系，但我认为它们不相关(department,budget)。

我需要找到并显示总是在做同样的项目（总是在一起）的工人 id（eid）。我只需要显示每一对。

我正在尝试这个：

SELECT  
DISTINCT (e1.eid , e2.eid)

FROM
employee as e1 , employee as e2 

WHERE

e1.eid<>e2.eid  
AND e1.eid in 
(SELECT eid,pid as A from onproject where pid IN(
 (select pid as B from onproject where eid = e2.eid )INTERSECT (select pid as AB from onproject where eid = e1.eid)))
AND 
e2.eid in 
(SELECT eid,pid as C from onproject where pid IN
 ((select pid as D from onproject where eid = e1.eid) intersect (select pid as AC from onproject where eid = e2.eid)))

标签： sqlpostgresql

解决方案

嗯。. . 想到自加入和聚合：

select op1.eid, op2.eid
from onproject op1 join
     onproject op2
     on op1.pid = op2.pid and op1.eid < op2.eid
group by op1, op2
having count(*) = (select count(*) from onproject op where op.eid = op1.eid) and
       count(*) = (select count(*) from onproject op where op.eid = op2.eid);

如果onproject给定项目中给定员工的重复项，则需要使用count(distinct)s 稍微修改查询。

推荐阅读