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

本题思路

1. 找出各个分组内的排序

	SELECT
      g.id as id,
      g.score as score,
      CAST(
        if(
          @ pre_job = g.job,
          @ cur_rank: = @ cur_rank + 1,
          @ cur_rank: = 1
        ) AS SIGNED
      ) ranking,
      @ pre_job: = g.job as job
    FROM
      grade g,
      (
        SELECT
          @ cur_rank: = 0,
          @ pre_job: = NULL
      ) r
    ORDER BY
      g.job,
      g.score DESC

2. 找出各个分组的中位数，我这里定义成了page_from以及page

	select
      job,
      IF(count(*) % 2 = 1, 1, 2) as page,
      ceil(count(*) / 2) as page_from
    from
      grade
    group by
      job

3. 聚合1，2结果并排序

SELECT
  b.id as id,
  b.job as job,
  b.score as score,
  b.ranking as t_rank
FROM
  (
    select
      job,
      IF(count(*) % 2 = 1, 1, 2) as page,
      ceil(count(*) / 2) as page_from
    from
      grade
    group by
      job
  ) a
  left join (
    SELECT
      g.id as id,
      g.score as score,
      CAST(
        if(
          @ pre_job = g.job,
          @ cur_rank: = @ cur_rank + 1,
          @ cur_rank: = 1
        ) AS SIGNED
      ) ranking,
      @ pre_job: = g.job as job
    FROM
      grade g,
      (
        SELECT
          @ cur_rank: = 0,
          @ pre_job: = NULL
      ) r
    ORDER BY
      g.job,
      g.score DESC
  ) b on a.job = b.job
  and b.ranking >= a.page_from
  and b.ranking < (a.page_from + a.page)
order by
  b.id;