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

# 思路：
# 1、针对每条记录，生成和每个岗位的记录数量
# 2、根据每个岗位记录数量是奇数、偶数两种情况，用case when生成start 和end字段.
select distinct t1.job
,(case when t1.cnt_score % 2 = 0 then round(t1.cnt_score / 2,0) 
 else round((t1.cnt_score+1)/2,0) end ) as start
 ,(case when t1.cnt_score % 2 = 0 then round(t1.cnt_score/2+1,0) 
  else round((t1.cnt_score+1)/2,0) end ) as end from 
  (
select g1.job 
# ,dense_rank() over(partition by g1.job order by g1.score asc) as rank_score
,count(*) over(partition by g1.job) as cnt_score 
from grade as g1 ) t1 order by t1.job