sql??

[yunxiang]

表：

学号 分数

1 50
2 56
3 87
4 40
5 23


查找结果：

学号 分数 名次
1 50 3
2 56 2
3 87 1
4 40 4
5 23 5

[victorycyz]

select * ,identity(int,1,1) into #t from table1 order by 分数 desc
select * from #t order by id
go

drop table #t

[dlpseeyou]

select * ,名次=(select sum(1) from test2 b where a.分数<=b.分数)from test2 a order by 学号

[azsoft]

declare @t Table (学号 varchar(10),分数 varchar(10))
insert into @t
select '1','50'
union all select '2','56'
union all select '3','87'
union all select '4','40'
union all select '5','23'

select 学号,分数 into #t from @t
select 分数,identity(int,1,1)as 名次 into #t1 from @t order by 分数 desc

select #t.学号,#t.分数,#t1.名次 from #t left join #t1 on #t1.分数=#t.分数

drop Table #t
drop Table #t1

[CrazyFor]

select *,(select count(*) from 表 bb where bb.分数<=aa.分数) as 名次 from 表 aa order by 学号

[azsoft]

select a.学号,a.分数,b.名次 from 表 Inner join (select 分数,identity(int,1,1)as 名次 from 表 order by 分数 desc
) b on a.分数=b.分数

[azsoft]

SELECT *,(select count(*)+1 from 表 B WHERE A.分数<B.分数) AS 名次 FROM 表 A ORDER BY A.学号