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

# 修改了一下大神的代码
SELECT job,
        floor((count(*)+1)/2) start ,
        ceiling((count(*)+1)/2) end 
from grade
group by job
order by job;

#使用窗口函数
SELECT g.job,
        if(mod(count(rak),2)=0,round(count(rak)/2),round(count(rak)/2)) start ,
        if(mod(count(rak),2)=0,round(count(rak)/2)+1,round(count(rak)/2)) end 
FROM
(
SELECT job,
    ROW_NUMBER() over (partition by job) rak
FROM grade ) g 
group by g.job
;

#使用关联子查询
select job,
        ( select if(mod(count(*),2)=0,
                    round(count(*)/2),
                    round(count(*)/2)) start                 
                    from grade g2 where g1.job=g2.job
        ) start,
         ( select if(mod(count(*),2)=0,
                    round(count(*)/2)+1,
                    round(count(*)/2)) end                
                    from grade g2 where g1.job=g2.job
        ) end
from grade g1 
group by job
order by job;