题解 | #考试分数(五)#

select t1.id,t1.job,t1.score,t1.rk from (select id,job,score,row_number() over (partition by job order by score desc) rk from grade ) t1
inner join
(select job
     , ceil(count(score)/2) as start
     , if(count(score)%2=0,ceil(count(score)/2)+1,ceil(count(score)/2)) as end
   from grade 
   group by job)  t2
on t1.job=t2.job 
where t1.rk=t2.start or t1.rk=t2.end
order by id

对表进行排名(select id,job,score

,(row_number()over(partition by job order by score desc))as s_rank

from grade)t1

与select job

     , ceil(count(score)/2) as start

     , if(count(score)%2=0,ceil(count(score)/2)+1,ceil(count(score)/2)) as end

   from grade

   group by job 进行内连接

再进行筛选排名与start或end相同时即是所需数据