sql server中排名的问题

下面的内容是百度上摘抄下来的。

rank,dense_rank,row_number区别

一：语法(用法)：
     rank() over([partition by col1] order by col2)
     dense_rank() over([partition by col1] order by col2)
     row_number() over([partition by col1] order by col2)
     其中[partition by col1]可省略。

二：区别
    三个分析函数都是按照col1分组内从1开始排序
   
    row_number() 是没有重复值的排序(即使两天记录相等也是不重复的)，可以利用它来实现分页
    dense_rank() 是连续排序，两个第二名仍然跟着第三名
    rank()       是跳跃拍学，两个第二名下来就是第四名

 理论就不多讲了，看了案例，一下就明白了
   
SQL> create table t(
  2   name varchar2(10),
  3   score number(3));

Table created

SQL> insert into t(name,score) 
  select '语文',60 from dual union all
  select '语文',90 from dual union all
  select '语文',80 from dual union all
  select '语文',80 from dual union all
  select '数学',67 from dual union all
  select '数学',77 from dual union all
  select '数学',78 from dual union all
  select '数学',88 from dual union all
  select '数学',99 from dual union all
  select '语文',70 from dual
 

10 rows inserted

SQL> select * from t;

NAME       SCORE
---------- -----
语文          60
语文          90
语文          80
语文          80
数学          67
数学          77
数学          78
数学          88
数学          99
语文          70

10 rows selected

SQL> select name,score,rank() over(partition by name order by score) tt from t;

NAME       SCORE         TT
---------- ----- ----------
数学          67          1
数学          77          2
数学          78          3
数学          88          4
数学          99          5
语文          60          1
语文          70          2
语文          80          3   <----
语文          80          3   <----
语文          90          5

10 rows selected

SQL> select name,score,dense_rank() over(partition by name order by score) tt from t;

NAME       SCORE         TT
---------- ----- ----------
数学          67          1
数学          77          2
数学          78          3
数学          88          4
数学          99          5
语文          60          1
语文          70          2
语文          80          3   <----
语文          80          3   <----
语文          90          4

10 rows selected

SQL> select name,score,row_number() over(partition by name order by score) tt from t;

NAME       SCORE         TT
---------- ----- ----------
数学          67          1
数学          77          2
数学          78          3
数学          88          4
数学          99          5
语文          60          1
语文          70          2
语文          80          3  <----
语文          80          4  <----
语文          90          5

10 rows selected

SQL> select name,score,rank() over(order by score) tt from t;

NAME       SCORE         TT
---------- ----- ----------
语文          60          1
数学          67          2
语文          70          3
数学          77          4
数学          78          5
语文          80          6
语文          80          6
数学          88          8
语文          90          9
数学          99         10

10 rows selected

大家应该明白了吧！呵呵！接下来看应用

一：dense_rank------------------查询每门功课前三名

select name,score from (select name,score,dense_rank() over(partition by name order by score desc) tt from t) x where x.tt<=3  

NAME       SCORE ---------- -----

数学          99

数学          88

数学          78

语文          90

语文          80

语文          80

6 rows selected

二:rank------------------语文成绩70分的同学是排名第几。   

select name,score,x.tt from (select name,score,rank() over(partition by name order by score desc) tt from t) x where x.name='语文' and x.score=70

NAME       SCORE         TT ---------- ----- ----------

语文          70               4    

三：row_number——————分页查询     

select xx.* from (select t.*,row_number() over(order by score desc) rowno from t) xx where xx.rowno between 1 and 3;

NAME       SCORE      ROWNO ---------- ----- ----------

数学          99          1

语文          90          2

数学          88          3