oracle等级函数,等级函数查询

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@]