2、列出与“SCOTT”从事相同工作的所有员工及部门名称,部门人数,领导姓名。
◆确定要使用的数据表:
●emp表:员工信息。
●dept表:部门名称。
●emp表:统计出部门人数。
●emp表:找到领导信息。
◆确定已知的关联字段:
●雇员和部门:emp.deptno = dept.deptno
●雇员和领导:emp.mgr = memp.empno
第一步:先找到SCOTT的工作,此查询返回单行单列的数据。
SELECT job FROM emp WHERE ename='SCOTT' ;
第二步:找到 从事该工作 的雇员信息。单行单列,一般用于WHERE或HAVING上,
但是,现在没有统计需要,所以,只在WHERE子句上使用。
SELECT e.ename, e.job, e.sal
FROM emp e
WHERE job=(
SELECT job FROM emp WHERE ename='SCOTT' ) ;
第三步:查找到对应的部门名称信息,加入dept表,同时要增加:消除掉显示的笛卡尔积的条件。
SELECT e.ename, e.job, e.sal, d.dname
FROM emp e, dept d
WHERE job=(
SELECT job FROM emp WHERE ename='SCOTT' )
AND e.deptno=d.deptno ;
第四步:部门人数可以单独进行统计,而后,在FROM子句之后使用。
SELECT e.ename, e.job, e.sal, d.dname, temp.count
FROM emp e, dept d, (
SELECT deptno dno, COUNT(empno) count FROM emp GROUP BY deptno ) temp
WHERE e.job=(
SELECT job FROM emp WHERE ename='SCOTT' )
AND e.deptno=d.deptno
AND temp.dno=d.deptno ;
第五步:查找出雇员对应的领导信息,使用emp表实现自身关联操作。
SELECT e.ename, e.job, e.sal, d.dname, temp.count, m.ename mgrname
FROM emp e, dept d, (
SELECT deptno dno, COUNT(empno) count FROM emp GROUP BY deptno ) temp, emp m
WHERE e.job=(
SELECT job FROM emp WHERE ename='SCOTT' )
AND e.deptno=d.deptno
AND temp.dno=d.deptno
AND e.mgr=m.empno ;
第六步:消除掉SCOTT数据。
SELECT e.ename, e.job, e.sal, d.dname, temp.count, m.ename mgrname
FROM emp e, dept d, (
SELECT deptno dno, COUNT(empno) count FROM emp GROUP BY deptno ) temp, emp m
WHERE e.job=(
SELECT job FROM emp WHERE ename='SCOTT' )
AND e.deptno=d.deptno
AND temp.dno=d.deptno
AND e.mgr=m.empno
AND e.ename<>'SCOTT' ;