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

select Q.id,Q.job,Q.score_g,Q.t
from(
select g.id,g.score score_g,B.end,B.start,B.job,C.score score_c,C.t
from(
		select A.job,
			round((case when A.n%2=1 then(A.n+1)/2 else A.n/2 end),0) start,
			round((case when A.n%2=1 then(A.n+1)/2 else A.n/2+1 end),0) end
		from (
				select job ,count(job) n from grade 
				group by job
				order by job 
		) A
)  B
left join grade g on g.job = B.job
left join (
		select job,score,rank() over(partition by job order by score desc) t
		from grade
) C on C.job = B.job 
) Q
where Q.score_g = Q.score_c and Q.t in(Q.end,Q.start)
order by Q.id asc,Q.t desc;