一张sc表有(s_id,c_id,score)
查询条件按照s_id的平均score排名次
首先
select s_id,avg(score) from sc group by s_id;
名次就是；比如说s_id=2的名次就是平均分比他大的人数+1，这样让上面2个表as T1,AS T2,T1.avg(score)>T2.avg(score)
select 1+(select count(*) from (select s_id,avg(score) as 平均成绩 from sc group by s_id) as T1 where T1.平均成绩>T2.平均成绩 )as RANK,s_id,平均成绩 from (select s_id,avg(score) as 平均成绩 from sc group by s_id) as T2 order by 平均成绩 desc;
这个可以完成查询了，如果平均成绩相等，count(distinct),但是我之前的想法是
select count(T1.平均成绩)+1 as Rank from (select s_id,avg(score) as 平均成绩 from sc group by s_id) as T1, (select s_id,avg(score) as 平均成绩 from sc group by s_id) as T2 where T1.平均成绩>T2.平均成绩;
然后查询的值是16，这个就难理解了
原文：http://www.cnblogs.com/bashala/p/3602894.html