626. 换座位 - 力扣（LeetCode）

1. 626. 换座位 - 力扣（LeetCode）

2. 目标

   1. 输入

      输入:座位表
      		id	student
      1	Abbot
      2	Doris
      3	Emerson
      4	Green
      5	Jeames

   2. 输出

      输出:新座位表
      		id	student
      1	Doris
      2	Abbot
      3	Green
      4	Emerson
      5	Jeames

3. 分析

   编写解决方案来交换每两个连续的学生的座位号。如果学生的数量是奇数，则最后一个学生的id不交换。 按 id 升序 返回结果表。

   输入:座位表

   输出:新座位表

   id

   student

   id

   student

   1

   Abbot

   1

   Doris

   2

   Doris

   2

   Abbot

   3

   Emerson

   3

   Green

   4

   Green

   4

   Emerson

   5

   Jeames

   5

   Jeames

   将id为奇数的学生换为下一行的学生,偶数的学生换为上一行的学生,则如果最后一个同学的数量是奇数则为null

   id

   student

   new_student

   查询新座位,如果新位置是null则输出为原同学

   id

   student

   1

   Abbot

   Doris

   1

   Doris

   2

   Doris

   Emerson

   2

   Abbot

   3

   Emerson

   Green

   3

   Green

   4

   Green

   Jeames

   4

   Emerson

   5

   Jeames

   5

   Jeames

4. 实现

   DROP TABLE IF EXISTS Seat;
   Create table If Not Exists Seat (id int, student varchar(255));
   Truncate table Seat;
   insert into Seat (id, student) values ('1', 'Abbot');
   insert into Seat (id, student) values ('2', 'Doris');
   insert into Seat (id, student) values ('3', 'Emerson');
   insert into Seat (id, student) values ('4', 'Green');
   insert into Seat (id, student) values ('5', 'Jeames');
   SELECT * FROM Seat;
   SELECT *,if(id%2=0,lag(student,1) OVER (),lead(student,1) OVER ())new_student
   FROM Seat
   ORDER BY id;
   WITH t1 AS ( SELECT *,if(id%2=0,lag(student,1) OVER (),lead(student,1) OVER ())new_student
   FROM Seat
   ORDER BY id )
   SELECT id,if(new_student IS NULL ,student,new_student)student
   FROM t1;

5. 小结

        lag,lead查询