SQL查询解决方案混乱，被group by和having子句混淆

So basically this is the database: Consider the relation schema of the COMPANY database given below

所以基本上这就是数据库：考虑下面给出的COMPANY数据库的关系模式

EMPLOYEE (fmane, minit, lname, ssn, birthdate, address, sex, salary, superssn, dno)     KEY: ssn
DEPARTMENT (dname, dnumber, mgrssn, mgrstartdate)               KEY: dnumber.
PROJECT  (pname, pnumber, plocation, dnum)                          KEY: pnumber.
WORKS_ON (essn, pno, hours)                                         KEY: (essn, pno)
DEPENDENT  (essn, dependent-name, sex, bdate, relationship)             KEY: (essn, dependent-name)

and the question is:

问题是：

Give the last name and SSN of those managers who work on two or more projects located in Cleveland.

提供在克利夫兰的两个或多个项目上工作的经理的姓氏和SSN。

The correction solution is like this:

修正解决方案是这样的：

select Lname
  from Employee e, Department d
where (e.ssn = d.mgrssn) 
   and  ssn in (
                      select  w.essn
                       from   works_on w, Project p
                      where w.pno = p.pnumber
                          and  p.plocation = 'Cleveland'
                      group by w.essn
                      having count(*) >= 2
                     )

My question is:

我的问题是：

Why is the works_on added?
为什么要添加works_on？
employee e and department d, why are they both needed? I thought we are only finding employee
员工e和部门d，他们为什么都需要？我以为我们只找到了员工
finally, the most confusing part is group by w.essn having count(*) >= 2, should the >=2 part be used for project p instead? since we are finding out the project number that are greater than 2?
最后，最令人困惑的部分是w.essn有count（*）> = 2，是否应该> = 2部分用于项目p？既然我们发现项目编号大于2？

If you guys have time, can you please put signs by the right solution to show which part belong to which employee and why are they needed. I really appreciate it.

如果你们有时间，可以通过正确的解决方案来标记哪些部分属于哪个员工，以及为什么需要这些。对此，我真的非常感激。

1 个解决方案

#1

We can try. Hope this helps. Your question is:

我们可以尝试。希望这可以帮助。你的问题是：

2.employee e and department d, why are they both needed? I thought we are only finding employee

2.employee e和d部门，为什么他们都需要？我以为我们只找到了员工

No, the requirement is not for finding employee, it is for finding Managers

不，要求不是寻找员工，而是寻找经理

1.Why is the works_on added?

1.为什么要添加works_on？

Without the works_on, how do you know **which employee worked on which project?**

没有works_on，你怎么知道哪个员工在哪个项目上工作？**

3.finally, the most confusing part is group by w.essn having count() >= 2, should the >=2 part be used for project p instead? since we are finding out the project number that are greater than 2?*

3.最终，最令人困惑的部分是w.essn有count（）> = 2，是否应该> = 2部分用于项目p？既然我们发现项目编号大于2？*

Are you looking for projects that involve 2 or more employees or are you looking for employees who have worked on 2 or more projects?

您是在寻找涉及2名或更多员工的项目，还是在寻找从事过2个或更多项目的员工？

#1