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

来给大家表演一手笛卡尔积
思路大概就是rank值在（四）start和end之间就好了

SELECT t1.id,t1.job,t1.score,t1.rk FROM
(SELECT *,rank()over(partition by job order by score desc) rk
FROM grade ) t1 JOIN 
(SELECT *
,ROUND(CASE MOD(COUNT(score),2) WHEN 1 THEN COUNT(score)/2+0.5
            ELSE COUNT(score)/2 end,0) start
,ROUND(CASE MOD(COUNT(score),2) WHEN 1 THEN COUNT(score)/2+0.5
            ELSE COUNT(score)/2+1 end,0) end
FROM grade
GROUP BY job 
order BY job) t2 on t1.job=t2.job
WHERE t1.rk between start and end
order by t1.id