表jh03有下列数据： name score aa 99 bb 56 cc 56 dd 77 ee 78 ff 76 gg 78 ff 501. 名次生成方式1,Score重复时合并名次 SELECT * , Place=(SELECT COUNT(DISTINCT Score) FROM jh03 WHERE Score >= a.Score) FROM jh03 a ORDER BY Place 结果 Name Score Place ---------------- ----------------- ----------- aa 99.00 1 ee 78.00 2 gg 78.00 2 dd 77.00 3 ff 76.00 4 bb 56.00 5 cc 56.00 5 ff 50.00 62. 名次生成方式2 , Score重复时保留名次空缺 SELECT * , Place=(SELECT COUNT(Score) FROM jh03 WHERE Score > a.Score) + 1 FROM jh03 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
name score
aa 99
bb 56
cc 56
dd 77
ee 78
ff 76
gg 78
ff 501. 名次生成方式1,Score重复时合并名次
SELECT * , Place=(SELECT COUNT(DISTINCT Score) FROM jh03 WHERE Score >= a.Score)
FROM jh03 a
ORDER BY Place
结果
Name Score Place
---------------- ----------------- -----------
aa 99.00 1
ee 78.00 2
gg 78.00 2
dd 77.00 3
ff 76.00 4
bb 56.00 5
cc 56.00 5
ff 50.00 62. 名次生成方式2 , Score重复时保留名次空缺
SELECT * , Place=(SELECT COUNT(Score) FROM jh03 WHERE Score > a.Score) + 1
FROM jh03 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
select * from city order by ord descid ord kt topic regtime
----------- ----------- ---- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------
11 11 1 上海 2008-08-26 11:08:31.920
10 10 1 北京 2008-08-26 11:08:22.000
9 9 1 云南 2008-08-26 11:08:17.437
8 8 1 江苏 2008-08-26 11:08:12.233
7 7 1 黑龙江 2008-08-26 11:08:07.530
6 6 1 辽宁 2008-08-26 11:08:03.060
5 5 1 吉林 2008-08-26 11:07:58.483
4 4 1 山西 2008-08-26 11:07:52.700
2 2 1 湖南 2008-08-26 11:07:41.467
1 1 1 湖北 2008-08-26 11:07:22.950