方法一:case
# Write your MySQL query statement below
select
(case
when MOD(id, 2) != 0 and counts != id then id + 1
when MOD(id, 2) != 0 and counts = id then id
else id - 1
end )as id, student
from Seat, (select count(*) as counts from Seat ) as seat_counts
order by id ASC;
方法二:位运算 COALESCE()函数
select a.id, COALESCE(b.student, a.student) as student
from seat a
left join seat b
on ((a.id + 1) ^ 1) - 1 = b.id
order by a.id;
补充:
a.id -> b.id
位运算:(a.id + 1) ^ 1) - 1
举例子:
1 + 1 —> 2 ^ 1 —> 3 —> 2
2 + 1 —> 3 ^ 1 —> 2 —> 1
3 + 1 —> 4 ^ 1 —> 5 —> 4
4 + 1 —> 5 ^ 1 —> 4 —> 3
5 + 1 —> 6 ^ 1 —> 7 —> 6
参考: https://leetcode.cn/problems/exchange-seats/solutions/50432/huan-zuo-wei-by-leetcode/
仅take notes,如有侵权,请联系删除,感谢!