一张 seat 座位表,平时用来储存学生名字和与他们相对应的座位 id。
其中纵列的 id 是连续递增的
小美想改变相邻俩学生的座位。
示例:
+---------+---------+
| id | student |
+---------+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+---------+---------+
输出结果如下:
+---------+---------+
| id | student |
+---------+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+---------+---------+
注意:
如果学生人数是奇数,则不需要改变最后一个同学的座位。
来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/exchange-seats
我的解答:
select
IF(s1.id%2!=0,IF(s1.id=@co and @exch%2!=0,s1.id,s1.id+1),s1.id-1) as id,
s1.student
from seat as s1
right join (
select se.id,@co:=se.id from seat as se left join (select id,@exch:=id from seat) as se1 on se.id=se1.id where se.id%2!=0 ) as s2
on s1.id=s2.id or s1.id=s2.id+1