查找在 2 个以上项目中一起工作的员工人数

发布于 2025-01-11 19:57:58 字数 1672 浏览 0 评论 0原文

现在我们有3张表，分别是employees、workson、project。

对于 employees 表（包含示例数据）

employeeid	name	sex
100	John	M
101	Jim	M
102	Sam	F
103	Quinn	F
400	Jane	F
401	Mary	F

对于 workson 表，我们有

员工	ID 项目 ID
101	4554
102	4554
103	4554
104	4554
101	4555
102	4555
401	4555
101	4556
102	4556
401	4556

对于projects表

projectid	projectName
4556	Zombies
4555	Umbrella Corp
4554	Evil

根据数据集，应该清楚的是，只有在超过两个项目是吉姆和萨姆。因此，这应该是预期的结果，即 2。

然而，我自己的代码似乎返回每个员工参与过的项目数，并检索了 3000+++ 行（每个员工）。当输出应该只是一个简单的整数时。

SELECT COUNT(e.employeeid)
FROM employees e
JOIN workson w ON e.employeeid = w.employeeid
GROUP BY e.employeeid
HAVING COUNT(w.projectid) > 9 ;

原文

Now we have 3 tables which are employees, workson, project.

For the employees table (with sample data)

employeeid	name	gender
100	John	M
101	Jim	M
102	Sam	F
103	Quinn	F
400	Jane	F
401	Mary	F

For the workson table we have

employeeid	projectid
101	4554
102	4554
103	4554
104	4554
101	4555
102	4555
401	4555
101	4556
102	4556
401	4556

For the projects table

projectid	projectName
4556	Zombies
4555	Umbrella Corp
4554	Evil

Based on the dataset, it should be clear that the only employees who worked together on more than 2 projects are Jim and Sam. Hence that should be the expected outcome which is 2.

My own code however seems to return the number of projects that each employee had worked in and retrieved rows of 3000+++(every single employee). When the output should only be a simple integer.

SELECT COUNT(e.employeeid)
FROM employees e
JOIN workson w ON e.employeeid = w.employeeid
GROUP BY e.employeeid
HAVING COUNT(w.projectid) > 9 ;

分享到QQ

分享到微博

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

发布评论

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

未央 2025-01-18 19:57:58

您需要将 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

< 条件确保诸如 (Jim, Jim, Evil) 之类的行和相反的对，例如 (Jim, Sam, Evil) <-> （山姆、吉姆、邪恶） 不包括在内。

结果包含成对的员工以及他们一起工作的项目数。添加 having 子句很简单。

员工 ID	员工 ID	一起工作
101	102	3
101	401	2
102	401	2
101	103	1
102	103	1
101	104	1
102	104	1
103	104	1

You need to join the workson with itself like so:

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

The < condition ensures that rows such as (Jim, Jim, Evil) and the opposite pairs such as (Jim, Sam, Evil) <-> (Sam, Jim, Evil) are not included.

The result contains pairs of employees and the count of projects where they worked together. It is trivial to add a having clause.

employeeid	employeeid	worked_together
101	102	3
101	401	2
102	401	2
101	103	1
102	103	1
101	104	1
102	104	1
103	104	1

回复收藏 0 原文

清晰传感 2025-01-18 19:57:58

您需要将表 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< /a>

You need to join the table workson with itself:

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

回复收藏 0 原文

~没有更多了~