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,这个就难理解了

sql查询1,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。