初始化sql数据
CREATE TABLE `t_student` (
`id` bigint NOT NULL,
`name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
`score` int DEFAULT NULL,
`class_id` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
INSERT INTO `t_student` VALUES (1, 'A', 75, 1);
INSERT INTO `t_student` VALUES (2, 'B', 78, 2);
INSERT INTO `t_student` VALUES (3, 'C', 74, 1);
INSERT INTO `t_student` VALUES (4, 'D', 85, 2);
INSERT INTO `t_student` VALUES (5, 'E', 80, 1);
INSERT INTO `t_student` VALUES (6, 'F', 82, 2);
INSERT INTO `t_student` VALUES (7, 'G', 98, 1);
INSERT INTO `t_student` VALUES (8, 'H', 90, 2);
INSERT INTO `t_student` VALUES (9, 'I', 90, 2);
mysql 8版本的开窗函数实现效果
select id,name,class_id,score,lag(score,1,0)
over (partition by class_id order by score desc) before_score from t_student;
image.png
mysql 8版本前实现
SELECT
c.id,
c.NAME,
c.class_id,
c.score,
c.before_score
FROM
(
SELECT
IF ( @id = a.class_id, @lagname := @score, @lagname := '' ) AS before_score,
@id := a.class_id AS aclass_id,
@score := a.score AS aafter_score,
a.*
FROM
( SELECT * FROM t_student ORDER BY class_id, score DESC ) a,
( SELECT @lagname := NULL, @id := 0, @score := NULL ) b
) c;
image.png