--示例数据
CREATE TABLE tb(Name varchar(10),Score decimal(10,2))
INSERT tb SELECT 'aa',99
UNION ALL SELECT 'bb',56
UNION ALL SELECT 'cc',56
UNION ALL SELECT 'dd',77
UNION ALL SELECT 'ee',78
UNION ALL SELECT 'ff',76
UNION ALL SELECT 'gg',78
UNION ALL SELECT 'ff',50

提问1. 名次生成方式1,Score重复时合并名次

/*--结果
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 6
--*/

提问2. 名次生成方式2,Score重复时保留名次空缺
/*--结果
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
--*/

高手帮助

[此贴子已经被作者于2007-1-17 10:49:11编辑过]

我建议最好在这个表中先设置一个含有主键的ID列，怎么样，实际上在查询的时候，如果不许要ID列，不显示出来既可
有了主键，这样就好办了，因为在这个过程中涉及到判断，可以使用存储过程来解决

if object_id('tb') is not null
drop table tb
go
CREATE TABLE tb(Name varchar(10),Score decimal(10,2))
INSERT tb SELECT 'aa',99
UNION ALL SELECT 'bb',56
UNION ALL SELECT 'cc',56
UNION ALL SELECT 'dd',77
UNION ALL SELECT 'ee',78
UNION ALL SELECT 'ff',76
UNION ALL SELECT 'gg',78
UNION ALL SELECT 'ff',50

select *,place=(select count(*) from (select distinct score from tb ) a where a.score<=b.score) from tb b order by Score

结果：
Name Score place
---------- ------------ -----------
ff 50.00 1
bb 56.00 2
cc 56.00 2
ff 76.00 3
dd 77.00 4
ee 78.00 5
gg 78.00 5
aa 99.00 6

（所影响的行数为 8 行）

select *,place=(select count(*) from tb where score<b.score)+1 from tb b order by Score

Name Score place
---------- ------------ -----------
ff 50.00 1
bb 56.00 2
cc 56.00 2
ff 76.00 4
dd 77.00 5
ee 78.00 6
gg 78.00 6
aa 99.00 8

（所影响的行数为 8 行）

谢谢了

1. drop table tb
CREATE TABLE tb(Name varchar(10),Score decimal(10,2),Place integer)
INSERT tb SELECT 'aa',99,0
UNION ALL SELECT 'bb',56,0
UNION ALL SELECT 'cc',56,0
UNION ALL SELECT 'dd',77,0
UNION ALL SELECT 'ee',78,0
UNION ALL SELECT 'ff',76,0
UNION ALL SELECT 'gg',78,0
UNION ALL SELECT 'ff',50,0

while (select count(*) from tb where place = 0) > 0
begin
update tb set place = (select max(place) from tb) +1 where score = (select max(score) from tb where place = 0)
end
select * from tb order by place

2.
CREATE TABLE tb(Name varchar(10),Score decimal(10,2))
INSERT tb SELECT 'aa',99
UNION ALL SELECT 'bb',56
UNION ALL SELECT 'cc',56
UNION ALL SELECT 'dd',77
UNION ALL SELECT 'ee',78
UNION ALL SELECT 'ff',76
UNION ALL SELECT 'gg',78
UNION ALL SELECT 'ff',50

select Name,Score,Place = (select count(*) from tb where score> a.score)+1 from tb a order by place

if object_id('tb') is not null
drop table tb
go
CREATE TABLE tb(Name varchar(10),Score decimal(10,2))
INSERT tb SELECT 'aa',99
UNION ALL SELECT 'bb',56
UNION ALL SELECT 'cc',56
UNION ALL SELECT 'dd',77
UNION ALL SELECT 'ee',78
UNION ALL SELECT 'ff',76
UNION ALL SELECT 'gg',78
UNION ALL SELECT 'ff',50

select *,place=(select count(*) from (select distinct score from tb ) a where a.score<=b.score) from tb b order by Score
语句改成
select *,place=(select count(distinct score) from a where a.score<=b.score) from tb b order by Score