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

sql script

-- find numbers of job
# select id, job, count(job) as max_number
# from grade
# group by job


-- rank
# select id, job, rank() over(partition by job order by score desc) as t_rank, score
# from grade



-- odd number
# select tmp2.id, tmp2.job, tmp2.score, tmp2.t_rank
# from (select job, count(job) as max_number
#       from grade
#       group by job) tmp1,
#       (select id, job, rank() over(partition by job order by score desc) as t_rank, score
#        from grade) tmp2
# where tmp1.job=tmp2.job
# and tmp1.max_number%2=1
# and t_rank=ceil(tmp1.max_number/2)


-- not odd number
# select tmp2.id, tmp2.job, tmp2.score, tmp2.t_rank
# from (select job, count(job) as max_number
#       from grade
#       group by job) tmp1,
#       (select id, job, rank() over(partition by job order by score desc) as t_rank, score
#        from grade) tmp2
# where tmp1.job=tmp2.job
# and tmp1.max_number%2=0
# and t_rank in (round(tmp1.max_number/2,0),round(tmp1.max_number/2,0)+1)



select tmp2.id, tmp2.job, tmp2.score, tmp2.t_rank
from (select job, count(job) as max_number
      from grade
      group by job) tmp1,
      (select id, job, rank() over(partition by job order by score desc) as t_rank, score
       from grade) tmp2
where tmp1.job=tmp2.job
and tmp1.max_number%2=1
and t_rank=ceil(tmp1.max_number/2)
union all
select tmp2.id, tmp2.job, tmp2.score, tmp2.t_rank
from (select job, count(job) as max_number
      from grade
      group by job) tmp1,
      (select id, job, rank() over(partition by job order by score desc) as t_rank, score
       from grade) tmp2
where tmp1.job=tmp2.job
and tmp1.max_number%2=0
and t_rank in (round(tmp1.max_number/2,0),round(tmp1.max_number/2,0)+1)
order by id