获得给定工作的最低限度的员工 [英] Get the minimum employees with a given job
问题描述
我有这张桌子:
Name Null? Type
-------------------------- -------- ------------
EMPLOYEENO NOT NULL NUMBER(4)
ENAME VARCHAR2(15)
JOB VARCHAR2(15)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER
COMM NUMBER
DEPTNO NUMBER(2).
我想让部门中拥有给定工作的最低员工人数(例如，所有拥有分析师"职位的员工).
I want to get the department with minimum employees who have a given job (for example all the employees with 'Analyst' job).
您能帮我查询一下吗?
推荐答案
这里的关键是获得每个部门从事特定工作的Employee的数量.在下面的查询中，这是通过子查询实现的.然后，我们希望获得最低编号的部门.员工的工作量，因此我们按升序对子查询返回的记录进行排序，然后使用rownum = 1选择第一个结果
Here the key is to get the count of Employee doing particular job in each department. In below query, this is achieved by subquery. Then, we want to get the Department with minimum no. of employee doing that job so we ordered the records returned by subquery in ascending and then select the first result by using rownum = 1
SELECT DEPTNO from (
SELECT COUNT(*) AS NO_OF_EMP , DEPTNO
FROM EMPLOYEE EMP
WHERE EMP.JOBNAME = 'Analyst'
GROUP BY DEPTNO
ORDER BY NO_OF_EMP ASC )
where ROWNUM = 1;
SELECT DEPTNO from (
SELECT COUNT(*) AS NO_OF_EMP , DEPTNO
FROM EMPLOYEE EMP
WHERE EMP.JOBNAME = 'Analyst'
GROUP BY DEPTNO
ORDER BY NO_OF_EMP ASC )
where ROWNUM = 1;
这篇关于获得给定工作的最低限度的员工的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
