等级函数查询

1.一般等级查询,使用order by 和 rownum完成.例如，查询最近两次做的snapshot的snap_id,

select snap_id from ( select * from dba_hist_snapshot a order by a.snap_id desc) where rownum<=2;

2.rank函数分析功能

其实oracle对于rank和dense_rank已经支持合计功能，不过这次我仅仅使用了其分析功能。具体语法如下：RANK ( ) OVER ( [query_partition_clause] order_by_clause )。

　　CREATE TABLE rank(subject VARCHAR2(100),mark NUMBER);

SQL> select * from rank;

SUBJECT MARK
-------------------------------------------------------------------------------- ----------
Math 20
Math 40
Math 40
Math 50
Math 70
English 90
English 80
English 85
English 85
Chinese 79
Chinese 93
Chinese 85
Chinese 98

13 rows selected

查出前三名

SQL> select * from (select rank() over(partition by subject order by mark desc) rk,a.* from rank a ) t where t.rk<4;

RK SUBJECT MARK
---------- -------------------------------------------------------------------------------- ----------
1 Chinese 98
2 Chinese 93
3 Chinese 85
1 English 90
2 English 85
2 English 85
1 Math 70
2 Math 50
3 Math 40
3 Math 40

10 rows selected

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16298743/viewspace-1046684/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16298743/viewspace-1046684/