Oracle子查询-阿里云开发者社区

SQL> --问题：要查询工资比SCOTT高的员工信息
SQL> --1. 查询SCOTT的工资
SQL> select sal from emp where ename='SCOTT';

       SAL
----------
      3000

SQL> --2. 查询比3000 高的
SQL> select *
2 from emp
3 where sal >3000;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ----------
    DEPTNO
----------
      7839 KING       PRESIDENT            17-11月-81           5000
        10

SQL> set linesize 120
SQL> col sal for 9999
SQL> /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
      7839 KING       PRESIDENT            17-11月-81      5000                    10

SQL> --子查询解决上面的问题
SQL> select *
2 from emp
3 where sal > ( select sal
4 from emp
5 where ename='SCOTT');

SQL> --书写子查询的注意事项:
SQL> /*
SQL> 1. 将子查询放入括号中
SQL> 2. 将子查询往右放
SQL> 3. 一般子查询不使用order by 但是在Top-n分析和分页中需要
SQL> 4. group by后，不能使用子查询
SQL> 5. select, from, where后面可以使用
SQL> 6. 主查询和子查询可以不是一张表，只要子查询返回的结果主查询可以使用就OK
SQL> 7. 可以将子查询视为一张新表.
SQL> */
SQL> --在from后面使用子查询
SQL> --查询部门名称为SALES的员工信息
SQL> select e.empno,e.ename
2 from emp e,dept d
3 where e.deptno=d.deptno
4 and d.dname='SALES';

     EMPNO ENAME
---------- ----------
      7499 ALLEN
      7521 WARD
      7654 MARTIN
      7698 BLAKE
      7844 TURNER
      7900 JAMES

已选择6行。

SQL> select *
2 from emp
3 where deptno= (select deptno
4 from dept
5 where dname ='SALES');

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-2月 -81      1600        300         30
      7521 WARD       SALESMAN        7698 22-2月 -81      1250        500         30
      7654 MARTIN     SALESMAN        7698 28-9月 -81      1250       1400         30
      7698 BLAKE      MANAGER         7839 01-5月 -81      2850                    30
      7844 TURNER     SALESMAN        7698 08-9月 -81      1500          0         30
      7900 JAMES      CLERK           7698 03-12月-81       950                    30

已选择6行。

SQL> -- 查询员工的名字和工资
SQL> select ename,sal
2 from emp;

ENAME        SAL
---------- -----
SMITH        800
ALLEN       1600
WARD        1250
JONES       2975
MARTIN      1250
BLAKE       2850
CLARK       2450
SCOTT       3000
KING        5000
TURNER      1500
ADAMS       1100

ENAME        SAL
---------- -----
JAMES        950
FORD        3000
MILLER      1300

已选择14行。

SQL> select *
2 from (select ename,sal)
3 ;
from (select ename,sal)
*
第 2 行出现错误:
ORA-00923: 未找到要求的 FROM 关键字

SQL> ed
已写入 file afiedt.buf

1 select *
2* from (select ename,sal from emp)
3 ;

ENAME        SAL
---------- -----
JAMES        950
FORD        3000
MILLER      1300

已选择14行。

SQL> --select后面跟子查询
SQL> select (select dname from deptno where deptno=10),ename
2 from emp;
select (select dname from deptno where deptno=10),ename
*
第 1 行出现错误:
ORA-00942: 表或视图不存在

SQL> select (select dname from dept where deptno=10),ename
2 from emp;

(SELECTDNAMEFR ENAME
-------------- ----------
ACCOUNTING     SMITH
ACCOUNTING     ALLEN
ACCOUNTING     WARD
ACCOUNTING     JONES
ACCOUNTING     MARTIN
ACCOUNTING     BLAKE
ACCOUNTING     CLARK
ACCOUNTING     SCOTT
ACCOUNTING     KING
ACCOUNTING     TURNER
ACCOUNTING     ADAMS

(SELECTDNAMEFR ENAME
-------------- ----------
ACCOUNTING     JAMES
ACCOUNTING     FORD
ACCOUNTING     MILLER

已选择14行。

SQL> --可以将子查询视为新表
SQL> select e.ename,e.sal
2 from (select ename,sal from emp) e;

ENAME        SAL
---------- -----
JAMES        950
FORD        3000
MILLER      1300

已选择14行。

SQL> --8.可以有多个子查询
SQL> --多行子查询
SQL> --查询部门名称为SALES和ACCOUNTING的员工信息
SQL> select *
2 from emp
3 where deptno=(select deptno
4 from dept
5 where dname='SALES' or dname='ACCOUTING');

已选择6行。

SQL> ed
已写入 file afiedt.buf

1 select *
2 from emp
3 where deptno in (select deptno
4 from dept
5* where dname='SALES' or dname='ACCOUTING')
SQL> /

已选择6行。

SQL> --查询工资最低的员工信息
SQL> select *
2 from emp
3 where sal = (select min(sal)
4 from emp);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80       800                    20

SQL> select *
2 from emp
3 where sal = ( select min(sal)
4                from emp
5                group by deptno);
where sal = ( select min(sal)
              *
第 3 行出现错误:
ORA-01427: 单行子查询返回多个行

SQL> -- 关于null
SQL> host cls

SQL> --查询不是经理的员工的信息
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80       800                    20
      7499 ALLEN      SALESMAN        7698 20-2月 -81      1600        300         30
      7521 WARD       SALESMAN        7698 22-2月 -81      1250        500         30
      7566 JONES      MANAGER         7839 02-4月 -81      2975                    20
      7654 MARTIN     SALESMAN        7698 28-9月 -81      1250       1400         30
      7698 BLAKE      MANAGER         7839 01-5月 -81      2850                    30
      7782 CLARK      MANAGER         7839 09-6月 -81      2450                    10
      7788 SCOTT      ANALYST         7566 13-7月 -87      3000                    20
      7839 KING       PRESIDENT            17-11月-81      5000                    10
      7844 TURNER     SALESMAN        7698 08-9月 -81      1500          0         30
      7876 ADAMS      CLERK           7788 13-7月 -87      1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
      7900 JAMES      CLERK           7698 03-12月-81       950                    30
      7902 FORD       ANALYST         7566 03-12月-81      3000                    20
      7934 MILLER     CLERK           7782 23-1月 -82      1300                    10

已选择14行。

SQL> select *
2 from emp
3 where empno not in (select mgr from emp)
4 ;

未选定行

SQL> ed
已写入 file afiedt.buf

1 select *
2 from emp
3* where empno not in (select mgr from emp where mgr is not null)
4 ;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80       800                    20
      7499 ALLEN      SALESMAN        7698 20-2月 -81      1600        300         30
      7521 WARD       SALESMAN        7698 22-2月 -81      1250        500         30
      7654 MARTIN     SALESMAN        7698 28-9月 -81      1250       1400         30
      7844 TURNER     SALESMAN        7698 08-9月 -81      1500          0         30
      7876 ADAMS      CLERK           7788 13-7月 -87      1100                    20
      7900 JAMES      CLERK           7698 03-12月-81       950                    30
      7934 MILLER     CLERK           7782 23-1月 -82      1300                    10

已选择8行。

SQL> --如果子查询中，包含null，类似表达式，会导致整个集合为null
SQL> host cls

SQL> -any的使用
SP2-0042: 未知命令 "-any的使用" - 其余行忽略。
SQL> --any的使用
SQL> --问题: 查询工资比10号部门员工中任意一个低的员工信息
SQL> select *
2 from emp
3 where sal < any (select sal from emp where deptno=10);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80       800                    20
      7900 JAMES      CLERK           7698 03-12月-81       950                    30
      7876 ADAMS      CLERK           7788 13-7月 -87      1100                    20
      7521 WARD       SALESMAN        7698 22-2月 -81      1250        500         30
      7654 MARTIN     SALESMAN        7698 28-9月 -81      1250       1400         30
      7934 MILLER     CLERK           7782 23-1月 -82      1300                    10
      7844 TURNER     SALESMAN        7698 08-9月 -81      1500          0         30
      7499 ALLEN      SALESMAN        7698 20-2月 -81      1600        300         30
      7782 CLARK      MANAGER         7839 09-6月 -81      2450                    10
      7698 BLAKE      MANAGER         7839 01-5月 -81      2850                    30
      7566 JONES      MANAGER         7839 02-4月 -81      2975                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
      7788 SCOTT      ANALYST         7566 13-7月 -87      3000                    20
      7902 FORD       ANALYST         7566 03-12月-81      3000                    20

已选择13行。

SQL> --等同于
SQL> select *
2 from emp
3 where sal < (select max(sal) from emp where deptno=10);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80       800                    20
      7499 ALLEN      SALESMAN        7698 20-2月 -81      1600        300         30
      7521 WARD       SALESMAN        7698 22-2月 -81      1250        500         30
      7566 JONES      MANAGER         7839 02-4月 -81      2975                    20
      7654 MARTIN     SALESMAN        7698 28-9月 -81      1250       1400         30
      7698 BLAKE      MANAGER         7839 01-5月 -81      2850                    30
      7782 CLARK      MANAGER         7839 09-6月 -81      2450                    10
      7788 SCOTT      ANALYST         7566 13-7月 -87      3000                    20
      7844 TURNER     SALESMAN        7698 08-9月 -81      1500          0         30
      7876 ADAMS      CLERK           7788 13-7月 -87      1100                    20
      7900 JAMES      CLERK           7698 03-12月-81       950                    30

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
      7902 FORD       ANALYST         7566 03-12月-81      3000                    20
      7934 MILLER     CLERK           7782 23-1月 -82      1300                    10

已选择13行。

SQL> --all的使用
SQL> --查询工资比10号部门都要低的员工信息
SQL> select *
2 from emp
3 where sal < all (select sal from emp where deptno=10);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------------- ----- ---------- ----------
      7369 SMITH      CLERK           7902 17-12月-80       800                    20
      7521 WARD       SALESMAN        7698 22-2月 -81      1250        500         30
      7654 MARTIN     SALESMAN        7698 28-9月 -81      1250       1400         30
      7876 ADAMS      CLERK           7788 13-7月 -87      1100                    20
      7900 JAMES      CLERK           7698 03-12月-81       950                    30

SQL> --等同于
SQL> ed
已写入 file afiedt.buf

1 select *
2 from emp
3* where sal < (select min(sal) from emp where deptno=10)
SQL> /

SQL> spool off
SQL> --课堂练习
SQL> --第一题: 伪列
SQL> select * from emp;

已选择14行。

SQL> -- rownum:行号
SQL> select rownum,empno,ename from emp;

    ROWNUM      EMPNO ENAME
---------- ---------- ----------
         1       7369 SMITH
         2       7499 ALLEN
         3       7521 WARD
         4       7566 JONES
         5       7654 MARTIN
         6       7698 BLAKE
         7       7782 CLARK
         8       7788 SCOTT
         9       7839 KING
        10       7844 TURNER
        11       7876 ADAMS

    ROWNUM      EMPNO ENAME
---------- ---------- ----------
        12       7900 JAMES
        13       7902 FORD
        14       7934 MILLER

已选择14行。

SQL> --关于rownum的注意事项：
SQL> --1. rownum一旦生成就不变
SQL> --2. rownum只能使用<=,不能使用>=
SQL> select rownum,ename
2 from emp
3 order by sal;

    ROWNUM ENAME
---------- ----------
         1 SMITH
        12 JAMES
        11 ADAMS
         3 WARD
         5 MARTIN
        14 MILLER
        10 TURNER
         2 ALLEN
         7 CLARK
         6 BLAKE
         4 JONES

    ROWNUM ENAME
---------- ----------
         8 SCOTT
        13 FORD
         9 KING

已选择14行。

SQL> select *
2 from emp
3 where rownum<=3
4 orber by sal desc;
orber by sal desc
*
第 4 行出现错误:
ORA-00933: SQL 命令未正确结束

SQL> ed
已写入 file afiedt.buf

1 select *
2 from emp
3 where rownum<=3
4* order by sal desc
SQL> /

SQL> host cls

SQL> select rownum,ename,sal
2 from emp
3 where rownum<3;

    ROWNUM ENAME        SAL
---------- ---------- -----
         1 SMITH        800
         2 ALLEN       1600

SQL> ed
已写入 file afiedt.buf

1 select rownum,ename,sal
2 from emp
3* where rownum>3
SQL> /

未选定行

SQL> --关于rownum的生成机制：必须从依次取出，然后付到记录上
SQL> --第二题：将子查询结果集视为新表
SQL> --第三题：
SQL> select hiredate from emp;

HIREDATE
--------------
17-12月-80
20-2月 -81
22-2月 -81
02-4月 -81
28-9月 -81
01-5月 -81
09-6月 -81
13-7月 -87
17-11月-81
08-9月 -81
13-7月 -87

HIREDATE
--------------
03-12月-81
03-12月-81
23-1月 -82

已选择14行。

SQL> --假设：我们已知道入职的年份有： 80 81 82 87
SQL> -- 第一题
SQL> select rownum,ename,sal
2 from (select * from emp order by sal desc)
3 where rownum<=3;

    ROWNUM ENAME        SAL
---------- ---------- -----
         1 KING        5000
         2 SCOTT       3000
         3 FORD        3000

SQL> select *
2 from (select rownum r,e1.*
3 from (select * from emp order by sal) e1
4 where rownum <=8
5 )
6 where r >=5;

         R      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- ---------- --------- ---------- -------------- ----- ---------- ----------
         5       7654 MARTIN     SALESMAN        7698 28-9月 -81      1250       1400         30
         6       7934 MILLER     CLERK           7782 23-1月 -82      1300                    10
         7       7844 TURNER     SALESMAN        7698 08-9月 -81      1500          0         30
         8       7499 ALLEN      SALESMAN        7698 20-2月 -81      1600        300         30

SQL> select e.empno,e.ename,e.sal,d.avgsal
2 from emp e,(select deptno, avg(sal) avgsal from emp group by deptno) d
3 where e.deptno=d.deptno
4 and e.sal>d.avgsal;

     EMPNO ENAME        SAL     AVGSAL
---------- ---------- ----- ----------
      7499 ALLEN       1600 1566.66667
      7566 JONES       2975       2175
      7698 BLAKE       2850 1566.66667
      7788 SCOTT       3000       2175
      7839 KING        5000 2916.66667
      7902 FORD        3000       2175

已选择6行。

SQL> --第三题
SQL> select hiredate from emp;

HIREDATE
--------------
17-12月-80
20-2月 -81
22-2月 -81
02-4月 -81
28-9月 -81
01-5月 -81
09-6月 -81
13-7月 -87
17-11月-81
08-9月 -81
13-7月 -87

HIREDATE
--------------
03-12月-81
03-12月-81
23-1月 -82

已选择14行。

SQL> select to_char(hiredate,'yyyy') from emp;

TO_C
----
1980
1981
1981
1981
1981
1981
1981
1987
1981
1981
1987

TO_C
----
1981
1981
1982

已选择14行。

SQL> select count(*) Total,
2         sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980"
3         sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981"
4         sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982"
5         sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987"
6 from emp;
       sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981"
       *
第 3 行出现错误:
ORA-00923: 未找到要求的 FROM 关键字

SQL> select count(*) Total,
2         sum(decode(to_char(hiredate,'yyyy'),'1980',1,0)) "1980",
3         sum(decode(to_char(hiredate,'yyyy'),'1981',1,0)) "1981",
4         sum(decode(to_char(hiredate,'yyyy'),'1982',1,0)) "1982",
5         sum(decode(to_char(hiredate,'yyyy'),'1987',1,0)) "1987"
6 from emp;

     TOTAL       1980       1981       1982       1987
---------- ---------- ---------- ---------- ----------
        14          1         10          1          2

SQL> spool off

Oracle子查询

热门文章

最新文章

相关电子书

相关实验场景

推荐镜像