题解 | #考试分数(四)#

-- 查询各个岗位分数升序排列之后的中位数位置的范围
-- 查找中位数范围：
-- 先分语言统计总数
-- 然后分两种情况：如果总数是偶数；如果是奇数
-- 并且按job升序排序
WITH c_s AS (
    SELECT 
        job,
        COUNT(score) OVER (PARTITION BY job) AS num
    FROM 
        grade
)
SELECT distinct
    job,
    CASE 
        WHEN MOD(num, 2) != 0 THEN round(CEIL(num / 2.0),0)
        ELSE round(num / 2,0)
    END AS start,
    CASE 
        WHEN MOD(num, 2) != 0 THEN round(CEIL(num / 2.0),0)
        ELSE round((num / 2) + 1,0)
    END AS end
FROM
    c_s
ORDER BY 
    job;

学会了用case when赋值变量的语法规范