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

/*
#请你写一个sql语句查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序，结果如下:

#岗位分组，中位值，grade信息，id升序

#--------------------无分段，找中位数===构造组内排名标签和中位数标签
with row_cnt as 
(select
*
,row_number()over(partition by job order by score desc ) as t_row
,count(score)over(partition by job) as t_cnt
from grade
)
#加两个标签，一个科目内排名(唯一序号row_number)，一个科目内人数总和
,model as
(select
*
,case
    when mod(t_cnt,2)=0 then t_cnt/2
    else CEILING(t_cnt/2)
end as start
,case
    when mod(t_cnt,2)=0 then (t_cnt/2)+1
    else CEILING(t_cnt/2)
end as end
from row_cnt
)  #把中位数标签列加进来


select
 id
,job
,score
,t_row as t_rank
from model
where t_row = start or t_row = end #科目内排名等于中位数序号就可以
order by id
*/

#用大神的正序降序比较法,正序降序大于等于元素个数/2
with row_cnt as 
(select
*
,row_number()over(partition by job order by score desc ) as t_desc
,row_number()over(partition by job order by score asc ) as t_asc
,count(score)over(partition by job)/2 as t_cnt
from grade
)

select
id	
,job	
,score	
,t_desc as t_rank
from row_cnt 
where t_desc >=t_cnt and t_asc >=t_cnt
order by id