数据库不限
table name : student id grade
1 90
2 85
3 95
4 70
要求列出id grade rank
3 95 1
1 90 2
2 85 3
4 70 4数据库刚刚学不久 现在碰到这个面试题 请各位指教
谢谢
table name : student id grade
1 90
2 85
3 95
4 70
要求列出id grade rank
3 95 1
1 90 2
2 85 3
4 70 4数据库刚刚学不久 现在碰到这个面试题 请各位指教
谢谢
select *,row_number() over(order by grade) as rank from tt) a order by rank
select *,row_number() over(order by grade) as rank from student order by rank
or
select a.id,a.grade from student a left join student b on a.grade<=b.grade
group by a.id,a.grade order by count(b.id)
group by a.id,a.grade order by count(b.id)select id,grade,(select count(*) from student where grade<=a.grade) as rank
from student a order by rank
set @A=0;
select id,grade,@A:=@A+1 as rank
from student_1 order by grade desc
create table student_1(id int,grade numeric(10,2));
insert into student_1 values(1,90),(2,85),(3,95),(4,70);set @A=0;
select id,grade,@A:=@A+1 as rank
from student_1 order by grade desc
select id,grade ,@num:=@num+1 as rank from student order by grade;
from student a,student b
where a.grade>=b.grade
group by a.grade desc
http://blog.csdn.net/ACMAIN_CHM/archive/2009/04/20/4095531.aspx