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

select id,job,score,t_rank from (select id,job,score,row_number() over(s order by score desc)  as t_rank,
case when 
(count(1) over s)%2=1
then convert(((count(1) over s)+1)/2,signed)
else convert((count(1)over  s)/2,signed)
end
as first,
case when 
(count(1) over s)%2=1
then convert(((count(1) over s)+1)/2,signed)
else convert((count(1)over s)/2+1,signed)
end
as second
from grade window  s as (partition by job))r1
where t_rank=first or t_rank=second
order by id

定义窗口s的时候不要加上排序，否则使用count的时候就不能求总数，而求的是累计