题解 | #考试分数(四)#
考试分数(四)
https://www.nowcoder.com/practice/502fb6e2b1ad4e56aa2e0dd90c6edf3c
select distinct job,start,end from ( select job,case when count(row_num) over(partition by job) % 2 = 1 then round(CEILING(count(row_num) over(partition by job) / 2),0) when count(row_num) over(partition by job) % 2 = 0 then round(count(row_num) over(partition by job) / 2,0) end as start, case when count(row_num) over(partition by job) % 2 = 1 then round(CEILING(count(row_num) over(partition by job) / 2),0) when count(row_num) over(partition by job) % 2 = 0 then round((count(row_num) over(partition by job) / 2) + 1,0) end as end from ( select id,job,score,row_number() over (partition by job order by score asc) row_num from grade ) a )b
1，先对原数据用窗口函数按照分数升序排列。
2，用count窗口函数计算每组长度%2判断是奇数还是偶数，奇数的start就向上取整，start和end相等。偶数的start为长度/2,end为 长度/2 + 1 。
3，分别用start，end列接收。
投递腾讯云智研发等公司7个岗位