一个成绩查询问题

[lihailing1987]

成绩查询，要考虑并列排名。
如学生表student
id name grade
1 a 90
2 b 85
3 c 90


要求查询后输出
id name grade rank
1 a 90 1
3 c 90 1
2 b 85 3


请问这个查询如何实现

[abuying]

select *,rank=row_number()over(order by grade desc) from tb

[冷箫轻笛]

select *,rank = (select count(1)+ 1 from student where id = a.id and grade > a.grade)
from student a

[abuying]

select *,rank=(selct count(1)+1 from tb a where a.name=tb.name and a.grade>=tb.grade)   from tb 

[lihailing1987]

[Quote=引用 1 楼 abuying 的回复:]

SQL code
select *,rank=row_number()over(order by grade desc) from tb
[/Quote]
这个输出是
id name grade rank
1 a 90 1
3 c 90 2
2 b 85 3

[lihailing1987]

[Quote=引用 2 楼 coolingpipe 的回复:]

SQL code

select *,rank = (select count(1)+ 1 from student where id = a.id and grade > a.grade)
from student a
[/Quote]
输出是
id name grade rank
1 a 90 1
3 c 90 1
2 b 85 1

[dawugui]

--sql 2000

create table student(id int,name varchar(10),grade int)
insert into student values(1 ,'a', 90)
insert into student values(2 ,'b', 85)
insert into student values(3 ,'c', 90)
go


select t.* , (select count(grade) from student where grade > t.grade) + 1 rank from student t
order by rank , id

drop table student

/*
id          name       grade       rank        
----------- ---------- ----------- ----------- 
1           a          90          1
3           c          90          1
2           b          85          3

（所影响的行数为 3 行）
*/

--sql 2005

create table student(id int,name varchar(10),grade int)
insert into student values(1 ,'a', 90)
insert into student values(2 ,'b', 85)
insert into student values(3 ,'c', 90)
go


select t.* , RANK() over(order by grade desc) rank from student t
order by rank , id

drop table student

/*
id          name       grade       rank
----------- ---------- ----------- --------------------
1           a          90          1
3           c          90          1
2           b          85          3

(3 行受影响)
*/

[华夏小卒]

--> 测试数据: #student
if object_id('tempdb.dbo.#student') is not null drop table #student
go 
create table #student (id int,name varchar(1),grade int)
insert into #student
select 1,'a',90 union all
select 2,'b',85 union all
select 3,'c',90

-- 2005
select *,rank= rank()over(order by grade desc) from #student

--2000
select *,rank=(select count(*)+1 from #student where grade>t.grade)
from #student t 
order by rank


id          name grade       rank
----------- ---- ----------- --------------------
1           a    90          1
3           c    90          1
2           b    85          3

[冷箫轻笛]

--不好意思，笔误了
create table student
(
id int,
name varchar(10),
grade int
)

insert into student select 1, 'a', 90
insert into student select 2, 'b', 85
insert into student select 3, 'c', 90

select *,rank = (select count(1)+ 1 from student where grade > a.grade)
from student a
order by rank


/*
id          name       grade       rank
----------- ---------- ----------- -----------
1           a          90          1
3           c          90          1
2           b          85          3

(3 行受影响)

*/

[冷箫轻笛]

事实证明，即使是一行代码，也有可能出bug

-_-!!

[lihailing1987]

[Quote=引用 6 楼 dawugui 的回复:]

--sql 2000
SQL code
create table student(id int,name varchar(10),grade int)
insert into student values(1 ,'a', 90)
insert into student values(2 ,'b', 85)
insert into student values(3 ,'c', 90)
go

……
[/Quote]
哥正解呀，谢谢了。

[hello926]

create table a(id int,name varchar(10),grade int)
insert into a
select 1, 'a', 90 union all
select 2, 'b', 85 union all
select 3, 'c', 90
go
select * ,rank=rank() over(order by grade desc) from a