TOP-N Query的五种写法(ORACLE)

来源：互联网发布：mac改用户名丢失管理员编辑：程序博客网时间：2024/04/27 23:14

第一种:最古老的,借助order by TOP-N column desc(desc前面N行，asc最后N行)的结果，
使用伪列rownum来实现TOP-5的查询：
SQL> select ename,sal
2 from
3 (select ename,sal from emp order by sal desc)
4 where rownum<=5
5 /

ENAME                       SAL
-------------------- ----------
KING                       5000
SCOTT                      3000
FORD                       3000
JONES                      2975
BLAKE                      2850

第二种：使用分析函数row_number(),如下：
SQL> select ename,sal
2 from
3 (select ename,sal,
4 row_number() over(order by sal desc) seq from emp)
5 where seq<=5
6 /

分析函数row_number()不仅可以实现TOP-N查询，而且还可以实现一个范围内的
查询，如：按ename的顺序查询第5到第10行：
SQL> select ename,sal,seq
2 from
3 (select ename,sal,
4 row_number() over(order by ename) seq from emp)
5 where seq between 5 and 10
6 /

ENAME                       SAL        SEQ
-------------------- ---------- ----------
FORD                       3000          5
JAMES                       950          6
JONES                      2975          7
KING                       5000          8
MARTIN                     1250          9
MILLER                     1300         10

已选择6行。

已用时间: 00: 00: 00.00
SQL>
也可以用row_number()来实现行列(列-->行)的转换：
SQL> select deptno,ename from emp order by 1;

    DEPTNO ENAME
---------- --------------------
        10 CLARK
        10 KING
        10 MILLER
        20 SMITH
        20 ADAMS
        20 FORD
        20 SCOTT
        20 JONES
        30 ALLEN
        30 BLAKE
        30 MARTIN

    DEPTNO ENAME
---------- --------------------
        30 JAMES
        30 TURNER
        30 WARD

已选择14行。

已用时间: 00: 00: 00.00
SQL> select deptno,
2   max(decode(seq,1,ename)) emp_1,
3   max(decode(seq,2,ename)) emp_2,
4   max(decode(seq,3,ename)) emp_3,
5   max(decode(seq,4,ename)) emp_4,
6   max(decode(seq,5,ename)) emp_5,
7   max(decode(seq,6,ename)) emp_6
8   from
9   (
10   select deptno,ename,
11   row_number() over(partition by deptno order by ename) seq from emp
12   )
13 group by deptno
14 /

    DEPTNO EMP_1      EMP_2      EMP_3      EMP_4      EMP_5      EMP_6
---------- ---------- ---------- ---------- ---------- ---------- ----------
        10 CLARK      KING       MILLER
        20 ADAMS      FORD       JONES      SCOTT      SMITH
        30 ALLEN      BLAKE      JAMES      MARTIN     TURNER     WARD

已用时间: 00: 00: 00.00
SQL>
第三种：使用分析函数dense_rank(),如下：
SQL> select ename,sal
2 from
3 (select ename,sal,
4 dense_rank() over(order by sal desc) seq from emp)
5 where seq<=5
6 /

ENAME                       SAL
-------------------- ----------
KING                       5000
SCOTT                      3000
FORD                       3000
JONES                      2975
BLAKE                      2850
CLARK                      2450

已选择6行。

已用时间: 00: 00: 00.00
SQL>
选择TOP-5，为什么会有6行数据呢？因为SCOTT的值等于FORD的值，这里它们只算一次。
如果使用rank(),则可以把重复的值作不同的计算,如第四种方法:

第四种:使用分析函数rank()，如下：
SQL> select ename,sal
2 from
3 (select ename,sal,
4 rank() over(order by sal desc) seq from emp)
5 where seq<=5
6 /

已用时间: 00: 00: 00.00
SQL>

第五种：使用分析函数count(*)，如下：
SQL> select ename,sal
2 from
3 (select ename,sal,
4 count(*) over(order by sal desc) seq from emp)
5 where seq<=5
6 /

已用时间: 00: 00: 00.00
SQL>