create table tb1 (
[name] varchar(20) not null,
score smallint not null
)
insert into tb1 values ('David',99)
insert into tb1 values ('Alex',33)
insert into tb1 values ('Scott',88)
insert into tb1 values ('Henry',13)
insert into tb1 values ('Edwards',96)
insert into tb1 values ('Robin',89)
insert into tb1 values ('Jones',85)
insert into tb1 values ('Lancy',71)
select *,identity(int,1,1) seq into #tb1 from tb1 order by score desc
select * from #tb1
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
GO
select idrow= identity(int,1,1) ,* into # from tb order by score desc,name
select name,Score ,
( select count(*) from # where idrow < a.idrow ) + 1 as plcace
from # a
drop table tb,#
/*
name Score plcace
---------- ------------ -----------
aa 99.00 1
ee 78.00 2
gg 78.00 3
dd 77.00 4
ff 76.00 5
bb 56.00 6
cc 56.00 7
ff 50.00 8
--示例数据
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
GO
--1. 名次生成方式1,Score重复时合并名次
SELECT *,Place=(SELECT COUNT(DISTINCT Score) FROM tb WHERE Score>=a.Score)
FROM tb 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 6
--*/
--2. 名次生成方式2,Score重复时保留名次空缺
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
--*/