2000用临时表 或者主键 或者唯一键 或者自增ID 辅助生成吧 select ID=(select count(*)from tb where 主键<=t.主键),* from tb t
select *,id=identity(int,1,1) into #tb from tb select * from #tb
如果score不重复. select t.* , (select count(1) from tb where score < t.score) + 1 from tb t表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
或者主键
或者唯一键
或者自增ID
辅助生成吧
select ID=(select count(*)from tb where 主键<=t.主键),*
from tb t
select * from #tb
select t.* , (select count(1) from tb where score < t.score) + 1 from tb t表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
2000下么有rownumber是否有rank()呢,两者功能差不多。。