ORACLE：子查询

最新推荐文章于 2024-05-06 21:10:29 发布

十三妹_

最新推荐文章于 2024-05-06 21:10:29 发布

阅读量2k

点赞数 1

分类专栏：数据库文章标签： oracle sql ocp

本文链接：https://blog.csdn.net/qq_41896822/article/details/127929554

版权

数据库专栏收录该内容

7 篇文章 3 订阅

订阅专栏

使用exists 和 not exists

#测试数据
select * from emp;

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      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
      7839 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
      8000 DEBUG

15 rows selected.
select * from dept;

    DEPTNO DNAME	  LOC
---------- -------------- -------------
	10 ACCOUNTING	  NEW YORK
	20 RESEARCH	      DALLAS
	30 SALES	      CHICAGO
	40 OPERATIONS	  BOSTON

子查询举例：

select t.* from (select * from emp order by sal desc) t where rownum<4;

由此可见使用子查询需要注意：

子查询要在括号内，括号内子查询只返回一行
子查询放在比较条件右侧增强可读性
单行操作符对应单行子查询，多行操作符对应多行子查询

一、单行子查询

只返回一行
比较操作符：= > >= < <= <>(<>不等于，等价于!=)

使用单行比较操作符

#例，查询工作与7369相同且工资大于7876的员工信息
select empno,ename,sal from emp where job=(select job from emp where empno=7369) and sal > (select sal from emp where empno=7876);

     EMPNO ENAME	     SAL
---------- ---------- ----------
      7934 MILLER	    1300

使用分组函数

#例 ，查询工资最低的员工
select empno,ename,sal from emp where sal=(select min(sal) from emp);

     EMPNO ENAME	     SAL
---------- ---------- ----------
      7369 SMITH	     800

上述子查询等价于：

select empno,ename,sal from emp order by sal asc fetch first 1 rows only;

子查询使用HAVING

使用HAVING

首先执行子查询
想主查询的having语句返回结果

#例 查询哪些部门最低工资比20号部门最低工资高
select deptno,min(sal) from emp group by deptno having min(sal) >(select min(sal) from emp where deptno=20);

    DEPTNO   MIN(SAL)
---------- ----------
	30	  950
	10	 1300
# 查询平均工资最高的职位
select job,avg(sal) from emp group by job having avg(sal)=(select max(avg(sal)) from emp group by job);
JOB	    AVG(SAL)
--------- ----------
PRESIDENT	5000
#为了测试子查询，但从结果来说有多种查询方法

注：单行子查询返回有多行结果时，会出错

ORA-01427：single-row subquery returns more than one row

二、多行子查询

1、操作符

IN 等于列表中的某一个值
ANY 与列表的任意值比较
ALL 与列表所有值比较

2、返回值多于一行

使用IN 和 NOT IN

#查询是领导的员工，mgr存的是员工的领导工号
select empno,ename from emp where empno in (select mgr from emp);

     EMPNO ENAME
---------- ----------
      7566 JONES
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7902 FORD

6 rows selected.

select empno,ename from emp where empno not in (select mgr from emp);

no rows selected

select empno,ename from emp where empno not in (select mgr from emp where mgr is not null);

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7654 MARTIN
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7934 MILLER

8 rows selected.

从上述可知，使用NOT IN时不能对null操作

使用ANY

#查询工资比职位是 clerk 的员工中任意(某)一个低的员工信息
select empno,ename,sal from emp where sal < any (select sal from emp where job='CLERK') and job !='CLERK';

     EMPNO ENAME	     SAL
---------- ---------- ----------
      7521 WARD 	    1250
      7654 MARTIN	    1250

附：

< any :小于最大值

> any :大于最小值

= any :等价于 in

使用ALL

# 例，查询工资比各部门平均工资都高的员工
select empno,ename,sal from emp where sal > all (select avg(sal) from emp group by deptno);

     EMPNO ENAME	     SAL
---------- ---------- ----------
      7566 JONES	    2975
      7788 SCOTT	    3000
      7902 FORD 	    3000
      7839 KING 	    5000

附：

< all :小于最小值

> all :大于最大值

!= all :等价于not in

多列子查询

例，查询与 7788 号员工 job,sal 相匹配的员工
select empno,ename,sal,job from emp where job=(select job from emp where empno=7788) and sal=(select sal from emp where empno=7788) and empno<>7788;

     EMPNO ENAME	     SAL JOB
---------- ---------- ---------- ---------
      7902 FORD 	    3000 ANALYST
等价于
select empno,ename,sal,job from emp where (job,sal) = (select job,sal from emp where empno=7788) and empno<>7788;

多行多列子查询要使用多行操作符，如：

select empno,ename,sal,job from emp

where (job,sal) in (select job,sal from emp where deptno=10) and empno<>7788;

在FROM中使用子查询

#例：查询比本部门平均工资高的员工
select a.empno,a.ename,a.sal,a.job,a.deptno,b.avgsal from emp a, (select deptno,avg(sal) avgsal from emp group by deptno) b where a.deptno=b.deptno and a.sal > b.avgsal;

     EMPNO ENAME	     SAL JOB	       DEPTNO	  AVGSAL
---------- ---------- ---------- --------- ---------- ----------
      7499 ALLEN	    1600 SALESMAN	   30 1566.66667
      7566 JONES	    2975 MANAGER	   20	    2175
      7698 BLAKE	    2850 MANAGER	   30 1566.66667
      7788 SCOTT	    3000 ANALYST	   20	    2175
      7839 KING 	    5000 PRESIDENT	   10 2916.66667
      7902 FORD 	    3000 ANALYST	   20	    2175

6 rows selected.

关联子查询

普通子查询：在主查询执行之前，子查询首先执行一次。子查询的结果要在主查询中使用。

关联子查询：需要重复执行子查询。（where写在子查询中）

#例：查询比本部门平均工资高的员工
select a.empno,a.ename,a.sal,a.deptno from emp a where a.sal > (select avg(sal) from emp b  where a.deptno=b.deptno);

     EMPNO ENAME	     SAL     DEPTNO
---------- ---------- ---------- ----------
      7499 ALLEN	    1600	 30
      7566 JONES	    2975	 20
      7698 BLAKE	    2850	 30
      7788 SCOTT	    3000	 20
      7839 KING 	    5000	 10
      7902 FORD 	    3000	 20

6 rows selected.

在SELECT中使用子查询

#例：查询员工所属的部门名
select empno,ename,deptno, (select dname from dept d where a.deptno=d.deptno) from emp a;

     EMPNO ENAME	  DEPTNO (SELECTDNAMEFR
---------- ---------- ---------- --------------
      7369 SMITH	      20 RESEARCH
      7499 ALLEN	      30 SALES
      7521 WARD 	      30 SALES
      7566 JONES	      20 RESEARCH
      7654 MARTIN	      30 SALES
      7698 BLAKE	      30 SALES
      7782 CLARK	      10 ACCOUNTING
      7788 SCOTT	      20 RESEARCH
      7839 KING 	      10 ACCOUNTING
      7844 TURNER	      30 SALES
      7876 ADAMS	      20 RESEARCH
      7900 JAMES	      30 SALES
      7902 FORD 	      20 RESEARCH
      7934 MILLER	      10 ACCOUNTING

14 rows selected.

使用exists 和 not exists

#，查询是经理的员工
select a.empno,a.ename from emp a where exists (select 1 from emp b where b.mgr=a.empno);

     EMPNO ENAME
---------- ----------
      7566 JONES
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7902 FORD

6 rows selected.
#，查询不是经理的员工
select a.empno,a.ename from emp a where not exists (select 1 from emp b where b.mgr=a.empno);

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7654 MARTIN
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7934 MILLER

8 rows selected.

三、集合运算符

集合运算符：

union 并集：排序、去重
intersect 交集：排序、去重
minus 差集：排序、去重

#创建测试表
create table test1 as select * from emp where deptno in (10,20);

Table created.

create table test2 as select * from emp where deptno in (20,30);

Table created.

union 并集：排序，去重

select empno,ename,deptno from test1 union select empno,ename,deptno from test2;

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

14 rows selected.

test1共8条数据，test2共11条数据，union去除了两表中重复的数据，且进行了排序，若不需要去重，使用union all；

select empno,ename,deptno from test1

union all select empno,ename,deptno from test2;

intersect 交集：排序、去重

select empno,ename,deptno from test1 intersect select empno,ename,deptno from test2;

     EMPNO ENAME	  DEPTNO
---------- ---------- ----------
      7369 SMITH	      20
      7566 JONES	      20
      7788 SCOTT	      20
      7876 ADAMS	      20
      7902 FORD 	      20

#查询是经理的员工号：
select empno from emp intersect select mgr from emp;

     EMPNO
----------
      7566
      7698
      7782
      7788
      7839
      7902

6 rows selected.

minus 差集：排序、去重

#test1中有test2中没有的数据：
select ename,deptno from test1 minus select ename,deptno from test2;

ENAME	       DEPTNO
---------- ----------
CLARK		   10
KING		   10
MILLER		   10

#test2中有test1中没有的数据：
select ename,deptno from test2 minus select ename,deptno from test1;

ENAME	       DEPTNO
---------- ----------
ALLEN		   30
BLAKE		   30
JAMES		   30
MARTIN		   30
TURNER		   30
WARD		   30

6 rows selected.

集合运算注意事项

注：

1、minus要注意操作符前后的表的顺序，如：test1 minus test2：参考test1，test2中没有的；

2、在进行集合运算时，两张表的列名可以不一样，但是数据类型必须一样（可以隐式转换都不行）

3、列column的个数必须一样

4、order by 只能在语句最后出现，且只能按第一个SELECT查询表中的列名来排序

上述第四点如：
alter table test2 rename column deptno to dpn;
select deptno,empno,ename from test1 union select dpn,empno,ename from test2 order by deptno;

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

14 rows selected.
#使用dpn则失败
select deptno,empno,ename from test1 union select dpn,empno,ename from test2 order by dpn;
select deptno,empno,ename from test1 union select dpn,empno,ename from test2 order by dpn
                                                                                      *
ERROR at line 1:
ORA-00904: "DPN": invalid identifier

十三妹_

关注

1
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
ORACLE：子查询

在主查询执行之前，子查询首先执行一次。子查询的结果要在主查询中使用。从上述可知，使用NOT IN时不能对null操作。需要重复执行子查询。（where写在子查询中）注：单行子查询返回有多行结果时，会出错。使用IN 和 NOT IN。在SELECT中使用子查询。IN 等于列表中的某一个值。ANY 与列表的任意值比较。ALL 与列表所有值比较。:等价于not in。在FROM中使用子查询。子查询使用HAVING。多行多列子查询要使用。
复制链接

扫一扫