Leetcode数据库题解-626. Exchange Seats

题目地址：https://leetcode.com/problems/exchange-seats/

题目：玛丽编写一个查询以改变邻近学生的座位,如果学生人数是奇数，则不需要改变最后一个座位。与其变换名称,不如将id为2n变换为2n-1,2n-1变换为2n (n=1,2,3....)。

第一种：该方法通不过测试，因为是两个sql语句。

update seat t1, seat t2
set t1.student = t2.student, t2.student=t1.student
where t1.id%2=1 and t2.id=t1.id+1;

select * from seat;

第二种：

select s.id,s.student from 
(select id-1 as id,student from seat where mod(id,2)=0
union
 select id+1 as id,student from seat where mod(id,2)=1 and id!=(select count(*) from seat)
 union
 select id,student from seat where mod(id,2)=1 and id=(select count(*) from seat)
) s
order by id

第三种：采用case来实现

select case when id=(select max(id) from seat) and mod(id,2)=1 then id
            when id<(select max(id) from seat) and mod(id,2)=1 then id+1
            when mod(id,2)=0 then id-1
            end as id,
            student
from seat
order by id