明智的员工部门，员工人数超过5名

小编典典

明智的员工部门，员工人数超过5名

sql

我想显示department_id带有的以及，并且计数应大于5，并且我想让一月份没有被雇用的员工。

我尝试了以下查询

SELECT * FROM EMPLOYEES 
WHERE DEPARTMENT_ID IN
 (
   SELECT DEPARTMENT_ID
    FROM EMPLOYEES
   GROUP BY DEPARTMENT_ID
   HAVING COUNT(*)>5 
 )
AND HIRE_DATE NOT LIKE '%JAN%';

但是在这里我没有数。我也想数。

阅读 190

2021-04-14

共1个答案

小编典典

SELECT department_ID, count(employee_id) as '# of Employees' FROM EMPLOYEES 
WHERE DEPARTMENT_ID IN
 (
   SELECT DEPARTMENT_ID
    FROM EMPLOYEES
   GROUP BY DEPARTMENT_ID
   HAVING COUNT(*)>5 
 )
AND HIRE_DATE NOT LIKE '%JAN%'
group by department_ID;

该查询返回department_id，并且由于我按department_id分组，因此将返回属于每个部门的员工数

输出看起来像这样

  Department_Id | # of Employees
        1             7
        2             6
        4             9

2021-04-14