sql查询1

一张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，这个就难理解了