oracle分析函数之over()

 1、统计名次

dense_rank() : 允许并列名次，名次不间断

dense_rank() over(patition by id order by score)   --按id分组对score排名

dense_rank() over(order by score) --不分组对score排名

 

 select id,area,stu_type,score,dense_rank() over(partition by id order by score) from students;  /*按id分组对score排名*/

 

ID AREA       ST      SCORE DENSE_RANK()OVER(PARTITIONBYIDORDERBYSCORE)
---------- ---------- -- ---------- -------------------------------------------
   1 222              j       68                               1
   1 111             g      80                               2
   1 111              j      80                               2
   1 222             g      89                               3
   2 222             g      60                               1
   2 222              j      65                               2
   2 111              j      70                               3
   2 111