求救,成绩排序难题,谢谢(100分)

表名为student<br>考号 &nbsp;姓名 &nbsp;年级 &nbsp;成绩 &nbsp;排名<br>101 &nbsp; &nbsp;A &nbsp; &nbsp; 1 &nbsp; &nbsp; 85<br>102 &nbsp; &nbsp;B &nbsp; &nbsp; 1 &nbsp; &nbsp; 70<br>103 &nbsp; &nbsp;C &nbsp; &nbsp; 1 &nbsp; &nbsp; 85<br>201 &nbsp; &nbsp;D &nbsp; &nbsp; 2 &nbsp; &nbsp; 90<br>202 &nbsp; &nbsp;E &nbsp; &nbsp; 2 &nbsp; &nbsp; 55<br>203 &nbsp; &nbsp;F &nbsp; &nbsp; 2 &nbsp; &nbsp; 40<br>请问如何分年级求出"排名",且成绩相同,排名一样.<br>我用以下方法,只是求出全部人的排位,但不是分年级排位,请问有什么方法<br>select 考号,姓名,年级,(select count(*)+1 from student where student&gt;a.student) as 排名 from student a

这样可以吗？<br>select 考号,姓名,年级,(select count(*)+1 from student where (student&gt;a.student)and( 年纪=a.年纪)) as 排名 from student a

这样就可以看看效果了<br>Select * <br>from <br>(select 考号,姓名,年级,成绩,<br>(select count(*)+1 from student where (成绩&gt;a.成绩)and( 年级=a.年级)) as 排名 <br>from student a<br>&nbsp<br>order by 年级,排名

SELECT 考号,姓名,年级,成绩,<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; (SELECT COUNT(*) + 1<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FROM student<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHERE (成绩&gt; a.成绩) AND (年级 = a.年级)) AS 排名<br>FROM student a<br>ORDER BY 年级，排名<br><br>楼上正解！

楼上的语句应该会得到如下结果：<br>101 A 1 85 1<br>103 C 1 85 1<br>102 B 1 70 3<br>也就是在同年纪中如果有成绩相同，则后面的排名必然会有排名数值跳开的情况，<br>当然，这也没有错。<br>－－<br>下面的语句则连续排名<br>select *<br>from student<br>left outer join<br>&nbsp; &nbsp; (select 年级,成绩,<br>&nbsp; &nbsp; &nbsp; &nbsp; (select count(*)+1 <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;from <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;(select 年级,成绩 <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; from student <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; group by 年级,成绩<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp b <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;where (a.成绩&lt;成绩)and( 年级=a.年级)<br>&nbsp; &nbsp; &nbsp; &nbsp; ) as 排名<br>&nbsp; &nbsp; &nbsp;from &nbsp; <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;(select 年级,成绩 <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; from student <br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; group by 年级,成绩<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp a<br>&nbsp; &nbsp; ) tb<br>on tb.成绩=student.成绩 and &nbsp;student.年级=tb.年级<br><br>原理是：先对各年级的成绩进行分组，并计算排名，再用student表在<br>年纪和成绩上对其进行左连接。

谢谢各位,也向各位学习