方法(一):比较原始的方法
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函数