Oracle - 子查询、TOP

1 子查询

sql 中查询是可以嵌套的，一个查询的结果可以作为另外一个查询的条件、表。

 SELECT    select_list

 FROM    table

 WHERE    expr operator

          (SELECT    select_list

          FROM    table);

理解子查询的关键在于把子查询当作一张表来看待，外层的语句可以把内嵌的子查询返回的结果当成一张表使用，子查询结果可以作为一个虚表被使用。注意，子查询要用括号括起来。子查询根据其返回结果可以分为单行子查询和多行子查询。

1.1 单行子查询

当子查询有单行时，可以取单行中的一个字段形成单个值用于条件比较，特别是当子查询结果为单行单列时，可以当成一个值用于比较。

 -- 查询雇员其薪资在雇员平均薪资以上的雇员

 select *

 from emp e

 where e.sal > (select avg(e.sal) "AVGSAL" from emp e);

1.2 多行子查询

多行子查询返回的结果可以作为表使用，通常结合in、some/any、all、exists。

 -- 查在雇员中有哪些人是管理者

 select e.*

 from emp e

 where e.empno in (select distinct e.mgr

                   from emp e

                   where e.mgr is not null);

1.3 from 后的子查询

子查询结果可以作为一张续表用于from后。

 -- 每个部门平均薪水的等级

 select vt0.deptno,vt0.avgsal,sg.grade

 from (select e.deptno,avg(e.sal) "AVGSAL"

       from emp e

       group by e.deptno) VT0, salgrade sg

 where vt0.avgsal between sg.losal and sg.hisal;

2 TOP - N

在 Oracle 中和 mysql 不一样，没有 top 关键字，只能通过 rownum 属性作为筛选条件，实现取“前 N 个几个”。

rownum：表示对查询结果的数据集记录的编号，从1开始。

 -- 查询前10名雇员

 select e.*,rownum

 from emp e

 where rownum <= 10;

当我们要查询前 6 - 10 名雇员的时候就出现了问题。rownum 后面接 “<”、“<=”时不会出现问题，但取的第一个结果不是 rownum = 1 时，就会出现结果为空集的问题。例如 rownum >= 1 结果正常，但是 rownum > 1 为空集。可以这么理解，rownum 是对结果集进行编号，当程序执行where rownum > 1 时，把结果的第一行排除掉了，但是此时的结果集第一个记录 rownum 由 2 变成了 1，于是继续把这一条记录也剔除掉，以此类推，最后所有数据都被剔除了。

要解决这个问题，采取的方法时将 rownum 固定住，也就是将原表和 rownum 合成一个新表，新表上 rn 字段表示行数，在这个新表上再作判断。

 --求薪水最高的第6到10名雇员

 SELECT *

 FROM (SELECT X.*, ROWNUM NUM

      FROM (SELECT E.*

           FROM EMP E

           ORDER BY E.SAL DESC) X)

 WHERE NUM BETWEEN 6 AND 10;

巴特西

Oracle - 子查询、TOP - N

1 子查询

1.1 单行子查询

1.2 多行子查询

1.3 from 后的子查询

2 TOP - N

最新文章

热门文章