我试图找出一个查询,该查询显示在多个部门中工作的员工人数(数量)。这里的表名和字段:
Employee
(id_employee,employee_name,薪水) Department
(id_dept,dept_name,预算) Department_Employee
(id_employee,id_dept,workhours_percentage)假设Department_Employee表的内容是
id_employee id_dept workhours_percentage
----------- ------- --------------------
0001 03 100
0001 04 50
0001 05 60
0002 05 60
0002 09 90
0003 08 80
0004 07 80
0005 06 60
0006 05 70
0007 04 75
0008 10 95
0009 02 25
0010 01 40
进行正确的查询后,结果应为2(员工),因为有2个员工在一个以上的部门中工作
我尝试了以下查询
SELECT COUNT(DISTINCT id_employee)
FROM Department_Employee
GROUP BY id_employee
HAVING COUNT(id_employee)>1
但是结果不对。
请帮帮我。
谢谢。
SELECT COUNT(*)
FROM
(
SELECT id_employee, COUNT(*) AS CNT
FROM Department_Employee
GROUP BY id_employee
) AS T
WHERE CNT > 1
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句