TOP-N Query的五种写法-服务器专区

TOP-N Query的五种写法

作者：网络　佚名编辑： IT168 2005-12-29 00:00

　　【IT168 服务器学院】 本质上它们都需要inline view

　　第一种:最古老的,借助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>
　　

关注我们