http://topic.csdn.net/u/20090920/15/eff2a6e7-5151-40c8-bf6e-2794570df10d.html?60546 SQL code
--1、2楼已经是正解？ 正解个屁，你多插入一行( 5 5 88 )试试！
--按成绩排名
id name grade
1 2 80
2 2 90
3 3 80
4 4 88
5 5 88
--是不是结果应该是：
id name grade rank
1 2 80 3
2 2 90 1
3 3 80 3
4 4 88 2
5 5 88 2
--可是你去运行1、2楼的SQL语句看看，看看是不是上面这个结果！
select id,name,grade,(select count(*) from yourTable where grade>a.grade)+1 as rank
from yourTable
order by grade desc;
--------My SQL方法一：(Distinct子查询法)--------------------
--优点：不用去定义变量，省事
--缺点：运行效率不如方法二
select id, name, grade, (SELECT count(distinct grade) from tb1 where grade>t1.grade)+1 as rank
from tb1 t1
order by grade desc;
--------My SQL方法二：(变量递增法)--------------------------
--优点：速度应该比方法一快
--缺点：在每次运行此查询之前，要定义并初始化为0的变量，用以排名，比较麻烦！
set @rownum=0;
select t1.id, t1.name, t1.grade, t2.rank
from tb1 t1 right join (select distinct @rownum:=@rownum+1 as rank, grade from tb1 order by grade desc) t2
on t1.grade=t2.grade
order by t1.id;
--------Oracle方法一：(rownum右联接法)----------------------
select t1.id, t1.name, t1.grade, t2.rank
from tb1 t1 left join ( select rownum as rank, grade from (select distinct grade from tb1 order by grade desc)) t2
on t1.grade=t2.grade
order by t1.id
