2021-05-02

换座位

需求：编写一个 SQL 查询，小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。

展示效果：

id	student
1	Doris
2	Abbot
3	Green
4	Emerson
5	Jeames

现在小美想改变相邻俩学生的座位（若学生人数为奇数，则无需改变最后一位同学的座位），现在需要你编写SQL输出小美想要的的结果。

Create table If Not Exists 39_seat(id int, student varchar(255));
Truncate table 39_seat;
insert into 39_seat (id, student) values (1, 'Abbot');
insert into 39_seat (id, student) values (2, 'Doris');
insert into 39_seat (id, student) values (3, 'Emerson');
insert into 39_seat (id, student) values (4, 'Green');
insert into 39_seat (id, student) values (5, 'Jeames');

最终SQL:

select 
      a.id,
      ifnull(b.student,a.student) as student 
from 
      39_seat as a 
left join
      39_seat as b 
on 
      (a.id%2=1 && a.id=b.id-1) || (a.id%2=0 && a.id=b.id+1) 
order by 
      a.id;