1、oracle中rank()、dense_rank、partition、over()、fetc的用法
rank()对表中的数据进行分级排序.
譬如有张学生成绩统计单的表STUDENT_RESULT
name number kemu fenshu
li 0113101 高数 90
zhang 0113098 高数 80
wang 0113077 高数 70
li 0113101 物理 80
zhang 0113098 物理 90
wang 0113077 物理 70
我如果要检索出表中高数的前两名和物理的前两名
那么可以通过使用 RANK()方法达到目的
--首先 通过kemu进行分类，然后按照fenshu降序排序
select rank() over(partition by kemu order by fenshu desc) rk,t.* from
student_result t
结果
rk name number kemu fenshu
1 li 0113101 高数 90
2 zhang 0113098 高数 80
3 wang 0113077 高数 70
1 zhang 0113098 物理 90
2 li 0113101 物理 80
3 wang 0113077 物理 70
--然后，检索出rk<=2的记录，即取出前两名
select * from (select rank() over(partition by kemu order by fenshu
desc)
rk,t.* from student_result t) as y where y.rk<=2;
结果
rk name number kemu fenshu
1 li 0113101 高数 90
2 zhang 0113098 高数 80
1 zhang 0113098 物理 90
2 li 0113101 物理 80
dense_rank()和 rank()的用法完全相同，不同的是在出现分数相同的情况下
如
name number kemu fenshu
li 0113101 高数 80
zhang 0113098 高数 80
wang 0113077 高数 70
li 0113101 物理 80
zhang 0113098 物理 90
wang 0113077 物理 70
select rank() over(order by fenshu desc) rk,t.* from student_result t
的结果为
rk name number kemu fenshu
1 zhang 0113098 物理 90
2 li 0113101 高数 80
2 zhang 0113098 高数 80
2 li 0113101 物理 80
5 wang 0113077 高数 70
5 wang 0113077 物理 70
而select dense_rank() over(order by fenshu desc) rk,t.* from student_result
t
结果为
rk name number kemu fenshu
1 zhang 0113098 物理 90
2 li 0113101 高数 80
2 zhang 0113098 高数 80
2 li 0113101 物理 80
3 wang 0113077 高数 70
3 wang 0113077 物理 70
附 row_number()的使用
select row_number() over(order by fenshu desc) rk,t.* from student_result
t
rk name number kemu fenshu
1 zhang 0113098 物理 90
2 li 0113101 高数 80
3 zhang 0113098 高数 80
4 li 0113101 物理 80
5 wang 0113077 高数 70
6 wang 0113077 物理 70
fetch n rows only 取出当前记录的前多少行
如
select row_number() over(order by user_no) from user_files fetch first 5
rows only;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2、rank() OVER与row_number() OVER的区别
PARTITION BY testid ORDER BY creat_time
asc根据列testid分组，按照creat_time降序排列。
rank() OVER对有相同的值，标记相同的值。row_number()
则不会。
故如果要进行排重操作，使用row_number() 将不会有重复记录的产生。
如下：
SQL> SELECT
A.*,
2 rank() OVER(PARTITION BY testid ORDER BY creat_time asc)
ROW_NUMBER
3 FROM TEST_20090526 A;
TESTID TESTNAME CREAT_TIME
ROW_NUMBER
---------- ---------- ---------- ----------
1 A1 20090520
1
2 A2 20090521 1
2 A3 20090522 2
2 A5 20090522 2
3 A4
20090523 1
SQL> SELECT A.*,
2 row_number() OVER(PARTITION BY
testid ORDER BY creat_time asc) ROW_NUMBER
3 FROM TEST_20090526 A
4
;
TESTID TESTNAME CREAT_TIME ROW_NUMBER
---------- ----------
---------- ----------
1 A1 20090520 1
2 A2 20090521 1
2 A3
20090522 2
2 A5 20090522 3
3 A4 20090523 1
