题解 | #找出每个学校GPA最低的同学#
考试分数(五)
http://www.nowcoder.com/practice/b626ff9e2ad04789954c2132c74c0512
select id,job,score,r
from(select job,s,if(c%2=1,s,s+1) g
from
(select job,max(r) c,round(case when max(r)%2=1 then (max(r)+1)/2
when max(r)%2=0 then max(r)/2 end,0) s
from
(select job,score,dense_rank()over(partition by job order by score desc) r
from grade
) a
group by job
)b
)d right join (select id,job,score,dense_rank()over(partition by job order by score desc) r
from grade
) a using(job)
where r=s or r=g
order by id
from(select job,s,if(c%2=1,s,s+1) g
from
(select job,max(r) c,round(case when max(r)%2=1 then (max(r)+1)/2
when max(r)%2=0 then max(r)/2 end,0) s
from
(select job,score,dense_rank()over(partition by job order by score desc) r
from grade
) a
group by job
)b
)d right join (select id,job,score,dense_rank()over(partition by job order by score desc) r
from grade
) a using(job)
where r=s or r=g
order by id
