牛客 妙解 不同岗位中位数位置的所有grade信息

1. 数据结构

-- 数据结构
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for grade
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`  (
  `id` int NOT NULL,
  `job` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `score` int NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

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

SET FOREIGN_KEY_CHECKS = 1;

2.实现SQL(他人写法，思路新奇，值得记录)

-- abs( t1.s_rank -( t1.num + 1 )/ 2 )< 1  中位数的位置
-- 窗口函数应用
SELECT
    id,
    job,
    score,
    s_rank 
FROM
    (
    SELECT
        *,(
            row_number() over ( PARTITION BY job ORDER BY score DESC )) AS s_rank,(
        count( score ) over ( PARTITION BY job )) AS num 
    FROM
        grade 
    ) t1 
WHERE
    abs( t1.s_rank -( t1.num + 1 )/ 2 )< 1 
ORDER BY
    id;