牛客网之考试分数（5）

一.题目概述： 牛客每次考试完，都会有一个成绩表(grade)，如下:

第1行表示用户id为1的用户选择了C++岗位并且考了11001分

。。。

第8行表示用户id为8的用户选择了前端岗位并且考了9999分

请你写一个sql语句查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序，结果如下:

解释：

第1行表示C++岗位的中位数位置上的为用户id为2，分数为10000，在C++岗位里面排名是第2 第2，3行表示Java岗位的中位数位置上的为用户id为4,5，分数为12000,13000，在Java岗位里面排名是第2,1 第4行表示前端岗位的中位数位置上的为用户id为7，分数为11000，在前端岗位里面排名是第2

(注意: sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5，sqlite四舍五入的函数为round，sqlite不支持floor函数，支持cast(x as integer) 函数，不支持if函数，支持case when …then …else …end函数，sqlite不支持自定义变量)

二.用例输入：

drop table if exists grade;
CREATE TABLE  grade(
`id` int(4) NOT NULL,
`job` varchar(32) NOT NULL,
`score` int(10) NOT NULL,
PRIMARY KEY (`id`));


INSERT INTO grade VALUES
(1,C++,11001),
(2,C++,10000),
(3,C++,9000),
(4,Java,12000),
(5,Java,13000),
(6,前端,12000),
(7,前端,11000),
(8,前端,9999);

三.分析： 中位数的概念运用到sql中时，应注意第1，2位置上的数据，如题即count(id) = 2时，中位数应为1，2，应把1，2位置上的数据全部展现出来。

四.思路： （1）.由题意 ”查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序“ （2）.表1 求出各个岗位的id总数 ==> 求出岗位的中位数并注意考虑特殊值（即当count = 2时，(count(id)+1)/2 应再加1，保证两个数都能取到值 eg: mysql:FLOOR((COUNT()+1)/2)+if(COUNT() % 2=1,0,1) other: (cast((count(id)+1)/2 AS INTEGER)+(case when count(id)%2=1 then 0 else 1 end)) as ‘end’ ） （3）.表2 保证id按score正确排序，联立表1即可求出score中位数

整合得

select B.* from
(select job, cast((count(id)+1)/2 AS INTEGER) as start , (cast((count(id)+1)/2 AS INTEGER)+(case when count(id)%2=1 then 0 else 1 end)) as end
from grade 
group by job) A
JOIN
(select g1.*, (
    select count(distinct g2.score) 
    from grade g2 
    where g2.score>=g1.score and g1.job=g2.job) as rank
from grade g1 ) B

on (A.job=B.job  and B.rank between A.start and A.end)
order by B.id

五.总结： sql执行顺序：from - where - group - select - order - limit

(select g1.*, (
    select count(distinct g2.score) 
    from grade g2 
    where g2.score>=g1.score and g1.job=g2.job) as rank
from grade g1 ) B

自写解析版

# 查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序
# 每个分组按分数查询，按id升/降序

# 中位数
SELECT 
  tmp_by_job.id,tmp_by_job.job,tmp_by_job.score from
	# 按岗位分组 列出排序后的表状态
	(select g1.id as id ,g1.job as job,g1.score as score, (
    select count(distinct g2.score) 
    from grade g2 
    where g2.score>=g1.score and g1.job=g2.job) as t_rank
    from grade g1 ) as tmp_by_job
	INNER JOIN
	# 找出每个岗位的id数
	# (cast((count(id)+1)/2 AS INTEGER)+(case when count(id)%2=1 then 0 else 1 end))
	(SELECT id,job,FLOOR((COUNT(*)+1)/2) as mid ,FLOOR((COUNT(*)+1)/2)+if(COUNT(*) % 2=1,0,1) AS end FROM grade GROUP BY job) as count_by_job
	on (tmp_by_job.job=count_by_job.job  and tmp_by_job.t_rank between count_by_job.mid and count_by_job.end)
	order by tmp_by_job.id