SQL查询问题

发布于 2024-08-12 08:52:33 字数 287 浏览 2 评论 0原文

考虑两个表。Employee和Project。Employee表有eid、ename等字段。Project表有pid、pname等字段。现在，由于一个员工可以在多个项目上工作，并且一个项目可以由多个员工完成，因此，很明显，这两个表之间存在多对多关系。打破多对多关系，并创建一个名为Assignment的新表，其中包含eid和pid作为外键。

问题是，我想找出那些在超过 1 个项目中相互合作过的员工对。例如，如果员工 A 和员工 B 在多个项目中相互合作过，那么他们就有资格进行上述查询结果。

请让我知道对上述问题的查询是什么。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

枯叶蝶 2024-08-19 08:52:33

这是一个非常常见且简单的解决方案：

SELECT a1.eid, a2.eid
FROM Assignment a1
JOIN Assignment a2 ON (a1.eid < a2.eid AND a1.pid = a2.pid)
GROUP BY a1.eid, a2.eid
HAVING COUNT(*) > 1;

此查询执行连接，将Assignment 表中的行与同一表中具有相同项目和不同员工的其他行进行匹配。我们使用 < 来比较员工 ID，这样我们就不会得到重复的配对。

然后我们使用 GROUP BY 来确保每对员工只有一行。

HAVING 子句仅选择那些具有多行的组，这些组是一起处理多个项目的员工对。

Here's a pretty common and straightforward solution:

SELECT a1.eid, a2.eid
FROM Assignment a1
JOIN Assignment a2 ON (a1.eid < a2.eid AND a1.pid = a2.pid)
GROUP BY a1.eid, a2.eid
HAVING COUNT(*) > 1;

This query does a join to match rows in the Assignment table to other rows in the same table with the same project and a different employee. We use < to compare the employee id's so we don't get duplicate pairings.

Then we use GROUP BY to make sure there's only one row for each pair of employees.

The HAVING clause picks only those groups that have multiple rows, which are the pairs of employees who have worked on multiple projects together.

回复收藏 0 原文

脱离于你 2024-08-19 08:52:33

这可以帮助你吗？

select ass1.eid, ass2.eid 
from Assignment ass1         
join Assignment ass2 on ass1.pid = ass2.pid
where ass1.eid < ass2.eid
group by ass1.eid, ass2.eid
having COUNT(0) > 1

This can help you?

select ass1.eid, ass2.eid 
from Assignment ass1         
join Assignment ass2 on ass1.pid = ass2.pid
where ass1.eid < ass2.eid
group by ass1.eid, ass2.eid
having COUNT(0) > 1

回复收藏 0 原文

大海や 2024-08-19 08:52:33

这就是我的写法，与 Bill 所写的非常相似，不同之处在于对不同项目进行计数，以防关系表中 (emp_id, prj_id) 没有唯一约束：

SELECT r1.emp_id, r2.emp_id, COUNT(DISTINCT r1.prj_id) cntProjects
  FROM r_emp_prj r1, r_emp_prj r2
 WHERE r1.emp_id < r2.emp_id
   AND r2.prj_id = r1.prj_id
 GROUP BY r1.emp_id, r2.emp_id HAVING COUNT(DISTINCT r1.prj_id) > 1

如果关系表还存储角色（ dev、lead、q&a 等）是项目中人员的，同一对（emp_id、prj_id）在关系表中可能有多个条目。

这也会检索名称：

SELECT r1.emp_id, emp1.name,
       r2.emp_id, emp2.name,
       COUNT(DISTINCT r1.prj_id) cntProjects
  FROM r_emp_prj r1, r_emp_prj r2,
       emp emp1, emp emp2
 WHERE r1.emp_id < r2.emp_id
   AND r2.prj_id = r1.prj_id
   AND emp1.id = r1.emp_id
   AND emp2.id = r2.emp_id
 GROUP BY r1.emp_id, emp1.name, r2.emp_id, emp2.name
HAVING COUNT(DISTINCT r1.prj_id) > 1

This is how I would write it, very similar to what Bill wrote with the difference being on counting the distinct projects in case there is no unique constrain on (emp_id, prj_id) in the relation table:

SELECT r1.emp_id, r2.emp_id, COUNT(DISTINCT r1.prj_id) cntProjects
  FROM r_emp_prj r1, r_emp_prj r2
 WHERE r1.emp_id < r2.emp_id
   AND r2.prj_id = r1.prj_id
 GROUP BY r1.emp_id, r2.emp_id HAVING COUNT(DISTINCT r1.prj_id) > 1

If the relation table also stores the role(dev, lead, q&a etc.) of the person in the project, it is possible to have multiple entries in the relation table for the same (emp_id, prj_id) pair.

This retrieves the names as well:

SELECT r1.emp_id, emp1.name,
       r2.emp_id, emp2.name,
       COUNT(DISTINCT r1.prj_id) cntProjects
  FROM r_emp_prj r1, r_emp_prj r2,
       emp emp1, emp emp2
 WHERE r1.emp_id < r2.emp_id
   AND r2.prj_id = r1.prj_id
   AND emp1.id = r1.emp_id
   AND emp2.id = r2.emp_id
 GROUP BY r1.emp_id, emp1.name, r2.emp_id, emp2.name
HAVING COUNT(DISTINCT r1.prj_id) > 1

回复收藏 0 原文

~没有更多了~