用row_number() 和dense_rank()得出按job分组后的序号和排名，作为主表d:

select *,row_number() over(partition by job order by score) rank1
,dense_rank() over(partition by job order by score desc) rank2
from grade g

将主表与上一题的start和end分别联结，取出需要的列，生成最终表c

select id,d.job,d.score,rank1,rank2,start,end from
(select *,row_number() over(partition by job order by score) rank1
,dense_rank() over(partition by job order by score desc) rank2
from grade g)d
left join
(select job,floor((count(*)+1)/2) as start from grade group by job) a
on d.job=a.job and d.rank1=a.start
left join
(select job,floor((count(*)+2)/2) as end from grade group by job) b
on d.job=b.job and d.rank1=b.end

对c表限定条件：c.start is not null or c.end is not null，取出规定字段即可

select c.id,c.job,c.score,c.rank2 as t_rank from
(select id,d.job,d.score,rank1,rank2,start,end from
(select *,row_number() over(partition by job order by score) rank1
,dense_rank() over(partition by job order by score desc) rank2
from grade g)d
left join 
(select job,floor((count(*)+1)/2) as start from grade group by job) a
on d.job=a.job and d.rank1=a.start
left join
(select job,floor((count(*)+2)/2) as end from grade group by job) b
on d.job=b.job and d.rank1=b.end)c
where c.start is not null or c.end is not null
order by  c.id