create database test1
go
use test1
go
create table Test
(F1 char(10),
F2 char(10))
--测试表
insert into Test
select 'a' F1,'1' F2
union
select 'b' F1,'2' F2
union
select 'c' F1,'3' F2
union
select 'd' F1,'3' F2
union
select 'e' F1,'4' F2
union
select 'f' F1,'4' F2
union
select 'g' F1,'4' F2
union
select 'h' F1,'7' F2
union
select 'i' F1,'9' F2
--插入数据
select id=identity(int,0,1),f1,f2 into #t from test order by F2 desc
select a.f1,a.f2,a.id+1-cast(id-cc-minn as Char(10)) as [名次]
from #t a,
(select f2,cc,minn from
(select f2,count(*)as cc,min(id)-count(*) as minn from #t group by f2) t)b
where a.f2=b.f2
order by a.f2 desc
--测试
drop table #t
drop table test