子查询
时间:20190228
作者:chj
子查询语法
- 子查询(内查询)在主查询(外查询)之前执行。
- 主查询使用子查询结果。
- 子查询是嵌入在另一个SELECT语句子句中的SELECT语句。可以使用子查询从简单语句构建功能强大的语句。当需要从表中选择具有依赖于表本身的数据的条件的行时,它们可能非常有用。
使用子查询注意事项
- 子查询要包还在括号内
- 将子查询放在比较条件的右侧增强可读性 ( 子查询可以出现在比较运算符的两侧)
- 单行操作符对应单行子查询,多行操作符对应多行子查询
WHERE 子句中使用子查询
例:查询公司内,比“SCOTT”工资高的员工信息
select ename,sal
from emp
where sal>(select sal from emp
where ename='SCOTT');
ENAME SAL
---------- ----------
KING 5000
FROM 子句中使用子查询
例:查看员工姓名、工资、部门编号和部门平均工资
select a.ename,a.sal,a.deptno,b.salavg
from emp a,
(select deptno,avg(sal) salavg
from emp
group by deptno) b
where a.deptno = b.deptno;
ENAME SAL DEPTNO SALAVG
---------- ---------- ---------- ----------
SMITH 800 20 2175
ALLEN 1600 30 1566.66667
WARD 1250 30 1566.66667
JONES 2975 20 2175
MARTIN 1250 30 1566.66667
BLAKE 2850 30 1566.66667
CLARK 2450 10 2916.66667
SCOTT 3000 20 2175
KING 5000 10 2916.66667
TURNER 1500 30 1566.66667
ADAMS 1100 20 2175
JAMES 950 30 1566.66667
FORD 3000 20 2175
MILLER 1300 10 2916.66667
SELECT 子查询
select e.empno,e.ename,e.deptno,
(select d.dname from dept d where e.deptno = d.deptno)
from emp e;
单行子查询
- 只返回一行
- 使用单行比较操作符
操作符 | 含义 |
---|---|
= | 等于 |
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
<> | 不等于 |
子查询中的空值问题
例:查询公司不是领导的员工
错误写法:因为mgr内有空值
select distinct e.ename,e.job
from emp e,emp m
where e.empno not in (select mgr from emp);
no rows selected
正确写法:
select distinct e.ename,e.job
from emp e,emp m
where e.empno not in (select nvl(mgr,0) from emp);
ENAME JOB
---------- ---------
WARD SALESMAN
SMITH CLERK
TURNER SALESMAN
JAMES CLERK
MARTIN SALESMAN
ADAMS CLERK
ALLEN SALESMAN
MILLER CLERK
多行子查询
- 返回多行
- 使用多行比较操作符
操作符 | 含义 |
---|---|
in | 等于列表中的某一个值 |
any | 与列表中的任意值比较 |
all | 与列表中的所有值相比较 |
any:使用方法
例:查询出员工工资比任意一个部门的平均工资高的员工信息
select ename,sal,deptno
from emp
where sal > any (select avg(sal) from emp
group by deptno);
ENAME SAL DEPTNO
---------- ---------- ----------
KING 5000 10
FORD 3000 20
SCOTT 3000 20
JONES 2975 20
BLAKE 2850 30
CLARK 2450 10
ALLEN 1600 30
all:使用方法
例:查询出员工工资比所有部门的平均工资高的员工信息
select ename,sal,deptno
from emp
where sal > all (select avg(sal) from emp
group by deptno);
ENAME SAL DEPTNO
---------- ---------- ----------
JONES 2975 20
SCOTT 3000 20
FORD 3000 20
KING 5000 10
结果集分页
- rownum 只能用“<”,不能使用“>”和"="。
例:
查看全部信息
select rownum,a.*
from (select rownum rn,ename,sal
from emp) a;
ROWNUM RN ENAME SAL
---------- ---------- ---------- ----------
1 1 SMITH 800
2 2 ALLEN 1600
3 3 WARD 1250
4 4 JONES 2975
5 5 MARTIN 1250
6 6 BLAKE 2850
7 7 CLARK 2450
8 8 SCOTT 3000
9 9 KING 5000
10 10 TURNER 1500
11 11 ADAMS 1100
12 12 JAMES 950
13 13 FORD 3000
14 14 MILLER 1300
只看前5行
select rownum,a.*
from (select rownum rn,ename,sal
from emp) a
where rownum<6;
ROWNUM RN ENAME SAL
---------- ---------- ---------- ----------
1 1 SMITH 800
2 2 ALLEN 1600
3 3 WARD 1250
4 4 JONES 2975
5 5 MARTIN 1250
只看6-10行,错误写法
select rownum,a.*
from (select rownum rn,ename,sal
from emp) a
where rownum between 6 and 10;
no rows selected
只看6-10行,正确写法
select rownum,a.*
from (select rownum rn,ename,sal
from emp) a
where a.rn between 6 and 10;
ROWNUM RN ENAME SAL
---------- ---------- ---------- ----------
1 6 BLAKE 2850
2 7 CLARK 2450
3 8 SCOTT 3000
4 9 KING 5000
5 10 TURNER 1500