There are two relevant tables: works_on and project. Project contains the following attributes: PName, PNo, PLocation and DNo (department number). Works_on contains the following attributes: SSN, PNo, hours.
有两个相关的表:works_on和project。项目包含以下属性:PName、PNo、PLocation和DNo(部门号)。Works_on包含以下属性:SSN、PNo、hours。
I want to only count the SSNs that appear more than twice and then provide the count value, PName and PNo.
我只想计算出现两次以上的ssn，然后提供count值、PName和PNo。
This is my attempt so far:
这是我迄今为止的尝试:
SELECT
P.PNo, P.PName,
COUNT(W.SSN) AS no_employees
FROM
project AS P INNER JOIN works_on AS W ON P.PNO = W.PNo
WHERE W.SSN IN (SELECT SSN FROM WORKS_ON GROUP BY SSN HAVING COUNT(SSN) > 2)
GROUP BY P.PNo
but I get the wrong PNo's and the wrong number of employees. I have been trying to figure out why this code will not give me projects with more than two employees. Please help me figure out what I am doing wrong.
但是我弄错了PNo和错误的员工数量。我一直在试图弄明白为什么这个代码不会给我超过两个员工的项目。请帮我找出我做错了什么。
1 个解决方案
#1
2
You don't need inner query, group by with having should do, e.g.:
你不需要内部查询，你可以用have should do，例如::
SELECT p.no, p.name, COUNT(w.ssn) as employees
FROM project p JOIN works_on w ON p.pno = w.pno
GROUP BY p.no, p.name
HAVING employees > 2;
#1
2
You don't need inner query, group by with having should do, e.g.:
你不需要内部查询，你可以用have should do，例如::
SELECT p.no, p.name, COUNT(w.ssn) as employees
FROM project p JOIN works_on w ON p.pno = w.pno
GROUP BY p.no, p.name
HAVING employees > 2;