LEETCODE 626. 换座位

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');

既然能找到这里，肯定是看过题目的。

具体要求我就不叙述了，贴sql完全是为了占版面。

说说我的想法。

1.根据id的奇偶改变id

select if(id%2,id+1,id-1) as id,student from seat order by id asc

但是不满足，当总数为奇数时不修改id的条件

2.根据count的奇偶性判断是否需要+/-1，再次失败，因为count(*) 只会查询一次

3.在每次select种加入count(*)

select if(if(id%2,id+1,id-1)>(select count(*) from seat),id,if(id%2,id+1,id-1)) as id,
student from seat order by id asc

get 执行时间 539ms，不知道怎么优化 

4.最后一次修改

select if(if(id%2,id+1,id-1)>(select id from seat order by id desc limit 1),id,if(id%2,id+1,id-1)) as id,
student from seat order by id asc

执行用时483ms