sql server 实现排名

 

方法（一）:比较原始的方法
SELECT *,Place=(SELECT COUNT(Score) FROM tb WHERE Score>a.Score)+1
FROM tb a
ORDER BY Place
/*--结果
Name       Score        Place
--------------- ----------------- -----------
aa         99.00        1
ee         78.00        2
gg         78.00        2
dd         77.00        4
ff         76.00        5
bb         56.00        6
cc         56.00        6
ff         50.00        8
--*/
方法（二）：使用RANK函数，例如对年龄进行排名
数据库数据为：
 Row Number   by Age    FirstName  Age
----------------------------            -----------
1                    Larry                        5
2                    Doris                       6
3                    George                   6
4                    Mary                       11
5                    Sherry                   11
6                    Sam                      17
7                    Ted                        23
8                    Marty                      23
9                    Sue                        29
10                   Frank                   38
11                   John                     40
SELECT RANK() OVER (ORDER BY Age) AS [Rank by Age],
FirstName,
Age
FROM Person
输出如下：
Rank by Age          FirstName  Age
-------------------- ---------- -----------
1                    Larry      5
2                    Doris      6
2                    George     6
4                    Mary       11
4                    Sherry     11
6                    Sam        17
7                    Ted        23
7                    Marty      23
9                    Sue        29
10                   Frank      38
11                   John       40

假如分组排序，如根据性别，可采用以下写法
SELECT RANK() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender],
FirstName, Age, Gender FROM Person
输出结果为

Partition by Gender  FirstName  Age         Gender
-------------------- ---------- ----------- ------
1                    Doris      6           F
2                    Mary       11          F
2                    Sherry     11          F
4                    Sue        29          F
1                    Larry      5           M
2                    George     6           M
3                    Sam        17          M
4                    Ted        23          M
4                    Marty      23          M
6                    Frank      38          M
7                    John       40          M

可以看到,按性别分组了,每个性别分组里,继续是用了rank函数