解决查询所需的帮助 [英] Help needed in solving a query

问题描述

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
 1254 TANS       MANAGER         5555 09-OCT-91       5000       5500         30
 123 VIDYA
 7369 SMITH      CLERK           7902 17-DEC-80        800                    20
 7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
 7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
 7566 JONES      MANAGER         7839 02-APR-81       2975                    20
 7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
 7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
 7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
 7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
 839 KING       PRESIDENT            17-NOV-81       5000                    10
 7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
 7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
 7900 JAMES      CLERK           7698 03-DEC-81        950                    30
 7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
 7934 MILLER     CLERK           7782 23-JAN-82       1300                   10

在此表中我想

显示所有仅为一名员工担任经理的员工。



我尝试了什么：

in this table i wants to
Display all the employees who is working as manager for only one employee.

What I have tried:

select *from emp where empno in (select mgr from emp where job = 'MANAGER')

i厌倦了这个SQL查询，但我不是确定天气是否正确。请帮我纠正。

i tired this sql query but i am not sure weather it is correct or not. please help me to correct.

推荐答案

首先使用GROUP BY来获取由同一个人管理的人数：

Start by using GROUP BY to get you the number of people who are managed by the same person:

SELECT Mgr, COUNT(mgr) As [Count] FROM emp
GROUP BY Mgr
HAVING COUNT(Mgr) = 1

然后您可以使用JOIN获取经理详细信息：

You can then use JOIN to get the manager details:

SELECT * FROM emp e
JOIN (SELECT mgr, COUNT(mgr) As [Count] FROM emp
      GROUP BY Mgr
      HAVING COUNT(Mgr) = 1) c
ON e.EmpNo = c.Mgr

这篇关于解决查询所需的帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！