mysql复制 lag很大,mysql 实现lag() over (partition by order by）

初始化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