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

-- 链接 LINK: https://www.nowcoder.com/practice/b626ff9e2ad04789954c2132c74c0512?tpId=82&tqId=35496&rp=1&ru=/exam/oj&qru=/exam/oj&sourceUrl=%2Fexam%2Foj%3Fdifficulty%3D5%26page%3D1%26pageSize%3D50%26search%3D%26tab%3DSQL%25E7%25AF%2587%26topicId%3D82&difficulty=5&judgeStatus=undefined&tags=&title=
-- 标题 TITLE: SQL270 考试分数(五)
-- 日期 DATE: 2023-02-25

/* 设计思路

**/

-- 题解 SQL
SELECT id, job, score, ranks
FROM (
    SELECT id, job, score,
        ROW_NUMBER() OVER(PARTITION BY job ORDER BY score DESC) AS ranks,
        COUNT(id) OVER(PARTITION BY job) AS num
    FROM grade
) T
WHERE FLOOR((num + 1.0) / 2) <= ranks AND ranks <= CEIL((num + 1.0) / 2)
ORDER BY id

;

-- 数据 DATA ===================================================
drop table if exists grade;
CREATE TABLE  grade(
`id` int(4) NOT NULL,
`job` varchar(32) NOT NULL,
`score` int(10) NOT NULL,
PRIMARY KEY (`id`));

INSERT INTO grade VALUES
(1,'C++',11001),
(2,'C++',10000),
(3,'C++',9000),
(4,'Java',12000),
(5,'Java',13000),
(6,'B',12000),
(7,'B',11000),
(8,'B',9999);