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

-- row_number
WITH t1 AS (
    -- todo1：总共多少人
    SELECT 
    	SUM(number) AS total   
    FROM class_grade
),
t2 AS (
	-- todo2 求累计值
    SELECT 
    	grade,
    	number,
    	SUM(number) OVER(ORDER BY grade ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_now
    FROM class_grade
    ORDER BY grade ASC
),
t3 AS (
    SELECT
        FLOOR((total+1) / 2) AS floor,
        CEIL((total+1) / 2) AS ceil
    FROM t1
),
t4 AS (
    SELECT
        grade,
        number,
        sum_now
    FROM t2

),
t5 AS (
    SELECT
        *
    FROM t4
    INNER JOIN t3
),
t6 AS (
    SELECT
        grade,
        sum_now,
        sum_now - floor AS floor_sub,
        sum_now - ceil AS ceil_sub
    FROM t5
),
t7 AS (
    SELECT
        min(floor_sub) AS min_floor
    FROM t6
    WHERE floor_sub >= 0
),
t8 AS (
    SELECT
        min(ceil_sub) AS min_ceil
    FROM t6
    WHERE ceil_sub >= 0
),
t9 AS (
    SELECT
        *
    FROM t6
    INNER JOIN t7,t8
    WHERE floor_sub = min_floor
    OR ceil_sub = min_ceil
),
t10 AS (
    SELECT
        grade
    FROM t9
    ORDER BY grade ASC
)
SELECT * FROM t10;
-- 求累计值的时候需要进行排序，间隔最小法则！