oracle子查询
子查询
所谓子查询，实际上为查询的嵌套
当根据给出的条件无法直接查出所需要的数据时,需要用到子查询.
子查询出现的位置
其中出现子查询最多的位置:
- where
- from
1用在where之后,作为条件判断;
查询与7369同部门的所有人
SQL> select empno,ename,deptno
2 from emp
3 where deptno=
4 (select deptno from emp where empno=7369);
EMPNO ENAME DEPTNO
---------- -------------------- ----------
7777 S_HH%GGH 20
7369 SMITH 20
7566 JONES 20
7788 SCOTT 20
7876 ADAMS 20
7902 FORD 20
2用在select之后,作为select的内容;
查询每个部门的人数
SQL> select
2 (select count(1) from emp where deptno=10) "10",
3 (select count(1) from emp where deptno=20) "20",
4 (select count(1) from emp where deptno=30) "30"
5 from dual;
10 20 30
---------- ---------- ----------
3 6 6
3可以用在having之后,用作分组数据判断的条件
查询平均工资 少于20组的平均工资 的班组
SQL> select deptno, avg(sal)
2 from emp
3 group by deptno
4 having avg(sal) >
5 (select avg(sal) from emp where deptno = 20);
DEPTNO AVG(SAL)
---------- ----------
10 2916.66667
4用在from之后,作为一个结果集:
查询最高工资的的五人的成绩
SQL> select *
2 from (select sal from emp where sal > 0 order by sal desc)
3 where rownum < 6;
SAL
----------
5000
3000
3000
2975
2850
子查询的使用方式
1子查询返回单行单列
要求查询公司工资最低的员工姓名,班组信息
第一步：统计出公司的最低工资
SQL> select min(sal) from emp;
MIN(SAL)
----------
800
第二步:上面会返回单行单列数据，是一个数值.
再进行where条件判断
SQL> select ename,job,deptno from emp
2 where sal=(select min(sal) from emp);
ENAME JOB DEPTNO
-------------------- ------------------ ----------
SMITH CLERK 20
查询公司雇佣最早的雇员
雇佣最早一定是雇员日期最小，那么使用MIN()函数完成
SQL> select min(hiredate) from emp;
MIN(HIREDATE)
--------------
17-12月-80
返回单行单列的数据，所有可以直接在WHERE子句中使用
SQL> select empno,ename,hiredate from emp
2 where hiredate=(select min(hiredate) from emp);
EMPNO ENAME HIREDATE
---------- -------------------- --------------
7369 SMITH 17-12月-80
2子查询返回单行多列
查询出与SMITH部门相同、职位相同的所有雇员的编号姓名信息
首先应该查询SMITH的部门与职位
SQL> select deptno,job from emp
2 where ename='SMITH';
DEPTNO JOB
---------- ------------------
20 CLERK
此时返回了单行两列的数据信息，要进行比较时要同时满足
SQL> select empno,ename,deptno,job from emp
2 where (deptno,job)=(
3 select deptno,job from emp where ename='SMITH');
EMPNO ENAME DEPTNO JOB
---------- -------------------- ---------- ------------------
7777 S_HH%GGH 20 CLERK
7369 SMITH 20 CLERK
7876 ADAMS 20 CLERK
3子查询返回多行多列
在WHERE子句中提供有三个主要的运算符：IN、ANY、ALL
SQL> select * from sc;
SNO CNO SCORE
-------------------- -------------------- ----------
s001 c001 78.9
s002 c001 80.9
s003 c001 81.9
s004 c001 60.9
s001 c002 82.9
s002 c002 72.9
s003 c002 81.9
s001 c003 59
查询c001课程比c002课程成绩高的所有学生的学号
SQL> select sno
2 from sc t1
3 where t1.cno='c001'
4 and sno in
5 (select sno from sc where
6 cno='c002' and t1.score>score and t1.sno=sno);
SNO
--------------------
s002
4exists
- 用于检查子查询是否至少返回一行数据
- 该子查询实际上并不返回任何数据,而是返回值True和False
查询c001课程比c002课程成绩高的所有学生的学号
SQL> select sno
2 from sc t1
3 where t1.cno='c001'
4 and exists(
5 select * from sc where
6 cno='c002' and t1.sno=sno and t1.score>score);
SNO
--------------------
s002
补充:排序函数
SQL> select * from sc;
SNO CNO SCORE
-------------------- -------------------- ----------
s001 c001 78.9
s002 c001 80.9
s003 c001 81.9
s004 c001 60.9
s001 c002 82.9
s002 c002 72.9
s003 c002 81.9
s001 c003 59
s004 c002 81.9
rank() over
-
查出指定条件后的进行排名
-
使用这个函数，成绩相同的两名是并列，下一位同学空出所占的名次。
查询各科成绩前三名的记录
SQL> select cno,sno,score,
2 rank() over(partition by cno order by score desc) ranks from sc;
CNO SNO SCORE RANKS
-------------------- -------------------- ---------- ----------
c001 s003 81.9 1
c001 s002 80.9 2
c001 s001 78.9 3
c001 s004 60.9 4
c002 s001 82.9 1
c002 s003 81.9 2
c002 s004 81.9 2
c002 s002 72.9 4
c003 s001 59 1
SQL> select cno,sno,score from
2 (select cno,sno,score,rank() over(partition by cno order by score desc) rank from sc)
3 where rank<4;
CNO SNO SCORE
-------------------- -------------------- ----------
c001 s003 81.9
c001 s002 80.9
c001 s001 78.9
c002 s001 82.9
c002 s003 81.9
c002 s004 81.9
c003 s001 59
使用rank over()的时候，空值是最大的，如果排序字段为null, 可能造成null字段排在最前面，影响排序结果。
SQL> select deptno,comm,
2 dense_rank() over(partition by deptno order by comm desc) ranks
3 from emp
4 where deptno=30;
DEPTNO COMM RANKS
---------- ---------- ----------
30 1
30 1
30 1400 2
30 500 3
30 300 4
30 0 5
SQL> select deptno,comm,
2 dense_rank() over(partition by deptno order by comm desc nulls last) ranks
3 from emp
4 where deptno=30;
DEPTNO COMM RANKS
---------- ---------- ----------
30 1400 1
30 500 2
30 300 3
30 0 4
30 5
30 5
dense_rank() over
与rank() over的区别是:
两名学生的成绩并列以后，下一位同学并不空出所占的名次。
SQL> select cno,sno,score,
2 dense_rank() over(partition by cno order by score desc) ranks from sc;
CNO SNO SCORE RANKS
-------------------- -------------------- ---------- ----------
c001 s003 81.9 1
c001 s002 80.9 2
c001 s001 78.9 3
c001 s004 60.9 4
c002 s001 82.9 1
c002 s003 81.9 2
c002 s004 81.9 2
c002 s002 72.9 3
c003 s001 59 1
SQL> select cno,sno,score from
2 (select cno,sno,score,dense_rank() over(partition by cno order by score desc) rank from sc)
3 where rank<4;
CNO SNO SCORE
-------------------- -------------------- ----------
c001 s003 81.9
c001 s002 80.9
c001 s001 78.9
c002 s001 82.9
c002 s003 81.9
c002 s004 81.9
c002 s002 72.9
c003 s001 59
row_number
该函数不需要考虑是否并列，那怕根据条件查询出来的数值相同也会进行连续排名
SQL> select cno,sno,score,
2 row_number() over(partition by cno order by score desc) ranks from sc;
CNO SNO SCORE RANKS
-------------------- -------------------- ---------- ----------
c001 s003 81.9 1
c001 s002 80.9 2
c001 s001 78.9 3
c001 s004 60.9 4
c002 s001 82.9 1
c002 s003 81.9 2
c002 s004 81.9 3
c002 s002 72.9 4
c003 s001 59 1
SQL> select cno,sno,score from
2 (select cno,sno,score,row_number() over(partition by cno order by score desc) ranks from sc)
3 where ranks<4;
CNO SNO SCORE
-------------------- -------------------- ----------
c001 s003 81.9
c001 s002 80.9
c001 s001 78.9
c002 s001 82.9
c002 s003 81.9
c002 s004 81.9
c003 s001 59
