考试分数（5）

题目：

牛客每次考试完，都会有一个成绩表(grade)，如下:

id	job	score
1	C++	11001
2	C++	11000
3	C++	9000
4	JAVA	12000
5	JAVA	13000
6	B	12000
7	B	11000
8	B	9999

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

。。。

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

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

id	job	score	t_rank
2	C++	10000	2
4	Java	12000	2
5	Java	13000	1
7	B	11000	2

解释：

第1行表示C++岗位的中位数位置上的为用户id为2，分数为10000，在C++岗位里面排名是第2

 

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

 

第4行表示B语言岗位的中位数位置上的为用户id为7，分数为11000，在前端岗位里面排名是第2

 

 

我的思路：

select id,b.job,score,t_rank from
(
　　(select job,
　　　　(case when count(score) %2=0 then count(score)/2 else (count(score)+1)/2 end) start,
　　　　(case when count(score) %2=0 then count(score)/2+1 else (count(score)+1)/2 end) end
　　from grade
　　group by job
　　) a
　　right join
　　(select id,job,score,dense_rank() over(partition by job order by score desc) t_rank
　　from grade
　　) b
on a.job = b.job
)
where t_rank between start and end
order by id

分析：

a表用来找出每个工作的中位数位置：

 

 b表用来进行工作排序

然后序号在start和end之间的提出来即为所求。

按理说中位数应该用row_number自然排名，而t_rank使用的是dene_rank,题目没有说明t_rank是那种排名，严谨应该在b表再加上row_number排序,筛选的t1_rank,比较的t2_rank

select id,b.job,score,t1_rank from
(
　　(select job,
　　　　(case when count(score) %2=0 then count(score)/2 else (count(score)+1)/2 end) start,
　　　　(case when count(score) %2=0 then count(score)/2+1 else (count(score)+1)/2 end) end
　　from grade
　　group by job
　　) a
　　right join
　　(select id,job,score,
　　　　dense_rank() over(partition by job order by score desc) t1_rank,
　　　　row_number()over(partition by job order by score desc) t2_rank
　　from grade
　　) b
　　on a.job = b.job
)
where t2_rank between start and end
order by id

 

方法一：

select id, job, score, t_rank
from
　　(
　　select id, job, score,
　　rank() over (partition by job order by score desc) t_rank,
　　count(*) over (partition by job) t_sum
　　from grade
　　) t
where t_rank between round(t_sum/2) and round((t_sum+1)/2)
order by id;

 

方法二：

select a.id,a.job,a.score,a.t_rank
from
　　(select id,job,score,
　　row_number()over(partition by job order by score desc) as t_rank

　　from grade)a

 

join (
　　select job,floor((count(*)+1)/2) as start,floor((count(*)+2)/2) as end
　　from grade
　　group by job
　　)b
on a.job=b.job
where a.t_rank=b.start or a.t_rank=b.end
order by id asc

 

方法三：

select id,job,score,s_rank from
　　(select a.id, a.job, a.score
　　,row_number() over(partition by a.job order by a.score desc) as s_rank
　　,count(*) over(partition by job) as t
　　from grade a

　　) b

where abs(b.s_rank-(b.t+1)/ 2.0)<1
order by b.id