Oracle修炼路程--子查询

子查询可以出现的位置：
where\select\having\from的后面；
不能出现在group by 后面；

1）select后面：必须是单行值；

SQL> select empno,ename,(select mgr from emp) from emp;
select empno,ename,(select mgr from emp) from emp
                    *
第 1 行出现错误:
ORA-01427: 单行子查询返回多个行


SQL> select empno,ename,(select mgr from emp where empno=7698) from emp;

     EMPNO ENAME      (SELECTMGRFROMEMPWHEREEMPNO=7698)
---------- ---------- ---------------------------------
      5741 QIN_JIALI                               7839
      7369 SMITH                                   7839
      7499 ALLEN                                   7839
      7521 WARD                                    7839
      7566 JONES                                   7839
      7654 MARTIN                                  7839
      7698 BLAKE                                   7839
      7782 CLARK                                   7839
      7788 SCOTT                                   7839
      7839 KING                                    7839
      7844 TURNER                                  7839
      7876 ADAMS                                   7839
      7900 JAMES                                   7839
      7902 FORD                                    7839
      7934 MILLER                                  7839

已选择15行。

--查询比SCOTT工资高的员工信息；
SQL> select *from emp where sal>(select sal from emp where ename='SCOTT');

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-4月 -81           4575                    20
      7698 BLAKE      MANAGER         7839 01-5月 -81           4450                    30
      7782 CLARK      MANAGER         7839 09-6月 -81           4050                    10
      7839 KING       PRESIDENT            17-11月-81           7000                    10

2）having后面；举例：查询最低工资比40号部门的最低工资高的部门编号；

SQL> select deptno,min(sal) from emp group by deptno having min(sal)>(select min(sal) from emp where deptno=40);

    DEPTNO   MIN(SAL)
---------- ----------
        10       1700

3. from后面：相当于修改了表结构；

SQL> select *from (select empno,ename from emp);

     EMPNO ENAME
---------- ----------
      5741 QIN_JIALI
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

已选择15行。

SQL> SELECT *FROM EMP WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME='SALES');

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-2月 -81           2000        300         30
      7521 WARD       SALESMAN        7698 22-2月 -81           1650        500         30
      7698 BLAKE      MANAGER         7839 01-5月 -81           4450                    30
      7844 TURNER     SALESMAN        7698 08-9月 -81           1900                    30
      7900 JAMES      CLERK           7698 03-12月-81           1350                    30

4）any和all的用法

--查询工资比30号部门中任意其中一名员工的工资高的员工信息；
SQL>  select *from emp where sal>any(select sal from emp where deptno=30);

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-11月-81           7000                    10
      7566 JONES      MANAGER         7839 02-4月 -81           4575                    20
      7698 BLAKE      MANAGER         7839 01-5月 -81           4450                    30
      7782 CLARK      MANAGER         7839 09-6月 -81           4050                    10
      7902 FORD       ANALYST         7566 03-12月-81           3400                    20
      7788 SCOTT      ANALYST         7566 19-4月 -87           3400                    20
      7499 ALLEN      SALESMAN        7698 20-2月 -81           2000        300         30
      7844 TURNER     SALESMAN        7698 08-9月 -81           1900                    30
      5741 QIN_JIALI  CLERK           7654 20-3月 -85           1900        600         20
      7934 MILLER     CLERK           7782 23-1月 -82           1700                    10
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1650       1400         40
      7521 WARD       SALESMAN        7698 22-2月 -81           1650        500         30
      7876 ADAMS      CLERK           7788 23-5月 -87           1500                    20

已选择13行。

SQL> select *from emp where sal>(select min(sal) from emp where deptno=30);

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      5741 QIN_JIALI  CLERK           7654 20-3月 -85           1900        600         20
      7499 ALLEN      SALESMAN        7698 20-2月 -81           2000        300         30
      7521 WARD       SALESMAN        7698 22-2月 -81           1650        500         30
      7566 JONES      MANAGER         7839 02-4月 -81           4575                    20
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1650       1400         40
      7698 BLAKE      MANAGER         7839 01-5月 -81           4450                    30
      7782 CLARK      MANAGER         7839 09-6月 -81           4050                    10
      7788 SCOTT      ANALYST         7566 19-4月 -87           3400                    20
      7839 KING       PRESIDENT            17-11月-81           7000                    10
      7844 TURNER     SALESMAN        7698 08-9月 -81           1900                    30
      7876 ADAMS      CLERK           7788 23-5月 -87           1500                    20
      7902 FORD       ANALYST         7566 03-12月-81           3400                    20
      7934 MILLER     CLERK           7782 23-1月 -82           1700                    10

已选择13行。


--查询工资比30号部门中全部员工的工资高的员工信息；
SQL> select *from emp where sal>(select max(sal) from emp where deptno=30);

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-4月 -81           4575                    20
      7839 KING       PRESIDENT            17-11月-81           7000                    10

SQL> select *from emp where sal>all(select sal  from emp where deptno=30);

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02-4月 -81           4575                    20
      7839 KING       PRESIDENT            17-11月-81           7000                    10

一般不在子查询中排序，如果排序用于分页查询；