【妙解】查询每组中位数位置上的记录

select id,job,score,s_rank
from 
(select *
        ,(row_number()over(partition by job order by score desc))as s_rank
	    ,(count(score)over(partition by job))as num
        from grade)t1
where abs(t1.s_rank-(t1.num+1)/2)<1
order by id;

select t1.id
        ,t1.job
        ,t1.score
        ,t1.s_rank
from 
(select id,job,score
        ,(row_number()over(partition by job order by score desc))as s_rank
        from grade)t1
join 
(select job
        ,case when count(score)%2=0 then ceiling(count(score)/2) else ceiling(count(score)/2)
        end as start1
        ,case when count(score)%2=0 then ceiling(count(score)/2+1) else ceiling(count(score)/2)
        end as end1
from grade
group by job)t2
on t1.job=t2.job 
where t1.s_rank=t2.start1&nbs***bsp;t1.s_rank=t2.end1
order by t1.id;