题解 | #最差是第几名(二)#

法一：

WITH t1 AS (
    SELECT grade, 
    (SUM(number) over (ORDER BY grade))-(number-1) AS min_rank,
    SUM(number) over (ORDER BY grade) AS max_rank
    FROM class_grade
    ORDER BY grade),
     t2 AS (
    SELECT ROUND(SUM(number)/2) AS medium_1, ROUND((SUM(number)+1)/2) AS medium_2 
     FROM class_grade)

SELECT grade FROM t1,t2
WHERE (medium_1 BETWEEN min_rank AND max_rank) OR (medium_2 BETWEEN min_rank AND max_rank)

思路是，第一步，找出每个分数的最好排名和最差排名。最差排名好求，最好排名是最差排名-（这个分数段的人数-1）。第二步，找到中位数。前面的题目告诉我们两个中位数是ROUND(总数/2)和ROUND((总数+1)/2)。第三步，作比较。中位数在最好排名和最差排名之间的则输出分数。

PS：关于中位数这个t2表没法和t1表融合，所以只能再做一个表（参考了讨论区）

法二，讨论区的妙解：

select grade from (select grade,(select sum(number) from class_grade) as total,
        sum(number) over(order by grade) a,
        sum(number) over(order by grade desc) b
        from class_grade) t1
where a >= total/2 and b >=total/2
order by grade;

想法是当分数档次的正序与逆序的人数累计均大于总人数的一半即为中位数处在这个分数档次。