给出了一个带有" deptid"one_answers"雇员"的员工表,我如何显示所有拥有超过部门20的员工的部门?我想在部门显示部门ID和员工人数。
到目前为止,我已经尝试过:
SELECT DeptId as "Dept Id", count(employeeId) as "Number of employee"
FROM EMPLOYEE
WHERE DeptId IN
(
SELECT DeptId
FROM EMPLOYEE
GROUP BY DeptId
)
AND DeptId <> 20
group by DeptId;
我似乎无法解决我需要找到比部门更多的部门的部门。
P.S总数newb
为了易于解释,想象一下您有以下#Employee
表:
+ ------ + -------------------- + ------- +
| Emp_Id | Emp_Name | Dept_Id |
+ ------ + -------------------- + ------- +
| 1 | Jim Ralston | 6 |
| 2 | Dave Davidson | 20 |
| 3 | Alexander The Great | 18 |
| 4 | Tribbidy Trump | 12 |
| 5 | Anglo Saxon | 6 |
| 6 | Henry Ford | 2 |
| 7 | Thomas Eddison | 9 |
| 8 | Running out of names | 4 |
| 9 | Two More | 4 |
| 10 | Done! | 4 |
+ ------ + -------------------- + ------- +
组成的dept_id组,并添加一个具有指定计数的子句应大于dept 20的计数:
select Dept_Id, COUNT(*) as NumberOfEmployees
from #Employee
group by Dept_Id
having COUNT(*) > (
select COUNT(*)
from #Employee
where Dept_Id = 20
)
给我们这个表:
+ ------- + ----------------- +
| Dept_id | NumberOfEmployees |
+ ------- + ----------------- +
| 4 | 3 |
| 6 | 2 |
+ ------- + ----------------- +
您需要其他逻辑(另一个"one_answers"(。您需要一个" group by"子句在" group by"之前运行的" group"子句,而不是" group by"。您的附加逻辑可以包括一个子查询,以查找部门20的规模。将结果与每个部门的大小进行比较。
SELECT DeptId AS "Dept Id", COUNT(EmployeeId) AS "Number of employee"
FROM EMPLOYEE
GROUP BY DeptId
HAVING
DeptId <> 20
AND COUNT(EmployeeId) > (SELECT COUNT(EmployeeId)
FROM EMPLOYEE
WHERE DeptId = 20);