按照各科成绩排序 按照总成绩排序

按各科成绩进行排序，并显示排名

set @rank=0;
select test.score.*,test.student.S_name,(@rank:=@rank+1)排名
from test.score,test.student
where test.score.s_no=test.student.s_no
order by test.score.s_score

查询学生的总成绩并进行排名

@rank := @rank + 1不能和group by 一起使用

解决办法: 将分组计算的结果作为一个虚拟表存在

正确写法:

set @rank=0;
select a.*, (@rank:=@rank+1)排名
from (select test.score.s_no,test.student.s_name,sum(test.score.s_score)total
from test.score,test.student,test.course
where test.score.s_no=test.student.s_no
group by test.score.s_no
order by total desc) as A