Oracle笔记10——Oracle高级子查询

一、嵌套子查询与相关子查询：

①嵌套子查询与相关子查询的特点
嵌套子查询：可以单独执行
相关子查询：不可以单独执行，依赖于父查询

②嵌套子查询与相关子查询的执行步骤
嵌套子查询：
1）先执行子查询
2）再使用步骤1）中的数据，交给父查询使用，用于确认或取消数据
相关子查询：
1）先执行父查询一次
2）将父查询得到的数据，交给子查询使用，用于确认或取消父查询数据
3）重复执行步骤1）、2），直到数据最后一行

--1.查询比本部门平均薪水高的员工姓名，薪水
--（1）嵌套子查询
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;--①部门平均薪水
SELECT e.ename, e.sal
  FROM emp e, (SELECT deptno, AVG(sal) avgsal FROM emp GROUP BY deptno) m
 WHERE e.deptno = m.deptno
   AND e.sal > avgsal;
--（2）相关子查询
SELECT e.ename, e.sal
  FROM emp e
 WHERE sal > (SELECT AVG(sal) FROM emp m WHERE e.deptno = m.deptno);
 
--2.查询所有部门名称和人数
--（1）嵌套子查询
SELECT deptno, COUNT(1) countone FROM emp GROUP BY deptno;--①查询所有部门人数
SELECT d.dname, countone
  FROM dept d,
       (SELECT deptno, COUNT(1) countone FROM emp GROUP BY deptno) m
 WHERE d.deptno = m.deptno;
--（2）相关子查询
SELECT dname, (SELECT COUNT(1) FROM emp WHERE dept.deptno = deptno) FROM dept;

--3.查询哪些员工是经理
--（1）嵌套子查询
SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL;--①查询有哪些经理编号
SELECT * FROM emp WHERE empno IN(SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL);
--（2）相关子查询
SELECT * FROM emp e1 WHERE (SELECT COUNT(1) FROM emp e2 WHERE e2.mgr = e1.empno) > 0;

--******4.查询每个部门工资前两名高的员工姓名，工资******
---------------------------
（1）10部门的员工：
1    CLARK    2450.00
2    KING    5000.00
3    MILLER    1300.00
其中，比1300工资高的有2人，比2450工资高的有1人，比5000工资高的有0人
------需要获取的是工资为2450和5000的员工信息
（2）20部门的员工：
1    SMITH    800.00
2    JONES    2975.00
3    SCOTT    3000.00
4    ADAMS    1100.00
5    FORD    3000.00
其中，比800工资高的有4人，比1100工资高的有3人，比2975工资高的有2人，比3000工资高的有0人
------需要获取的是工资为3000的两个员工信息
（3）30部门的员工：
1 ALLEN 1600.00
2 WARD  1250.00
3 MARTIN  1250.00
4 BLAKE 2850.00
5 TURNER  1500.00
6 JAMES 950.00
其中，比950工资高的有5人，比1250工资高的有3人，比1500工资高的有2人，比1600工资高的有1人，比2850工资高的人有0人
------需要获取的是工资为1600和2850的员工信息
结论：为了获取每个部门工资排前两名的员工信息，只需要判断该员工的工资大于该部门员工的工资的个数是否小于等于1，即
SELECT ename, sal, deptno FROM emp e1 WHERE (SELECT COUNT(1) FROM emp e2 WHERE e2.deptno = e1.deptno AND e2.sal > e1.sal) <= 1;

 

二、EXISTS 与 NOT EXISTS

--（1）查询哪些员工是经理？
SELECT * FROM emp e1 WHERE (SELECT COUNT(1) FROM emp e2 WHERE e2.mgr = e1.empno) > 0; --效率低
SELECT * FROM emp e1  WHERE EXISTS (SELECT * FROM emp e2 WHERE e2.mgr = e1.empno);    --效率高

--（2）查询哪些员工不是经理？
SELECT * FROM emp e1 WHERE (SELECT COUNT(1) FROM emp e2 WHERE e2.mgr = e1.empno) = 0;--效率低
SELECT * FROM emp e1  WHERE NOT EXISTS (SELECT * FROM emp e2 WHERE e2.mgr = e1.empno);--效率高