【leetcode】Exchange Seats

Mary is a teacher in a middle school and she has a table seat storing students' names and their corresponding seat ids.

The column id is continuous increment.
Mary wants to change seats for the adjacent students.
Can you write a SQL query to output the result for Mary?
+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Abbot   |
|    2    | Doris   |
|    3    | Emerson |
|    4    | Green   |
|    5    | Jeames  |
+---------+---------+
For the sample input, the output is:
+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Doris   |
|    2    | Abbot   |
|    3    | Green   |
|    4    | Emerson |
|    5    | Jeames  |
+---------+---------+
Note:
If the number of students is odd, there is no need to change the last one's seat.

 

解题思路：这个题目难度不大，第一眼看起来也许觉得有些繁琐，但是如果抽丝剥茧，将解题过程分解成以下几步，就会豁然开朗。

1.把所有奇数行的名字换成相邻的偶数行的名字。

select a.id,b.student from seat a left join seat b on a.id & 1  and a.id + 1 = b.id;

2.把所有偶数行的名字换成相邻的奇数行的名字。

select a.id,b.student from seat a left join seat b on a.id & 1 = 0  and a.id  - 1 = b.id;

3.合并1和2的结果

 select a.id,b.student  from seat a left join seat b
 on  (case when a.id & 1 then  a.id + 1 = b.id  else a.id -1 = b.id end);

4.如果总行数为奇数，最后一行的名字不用替换。修正SQL如下：

select a.id,case when b.student is null then a.student else b.student end  as student from seat a left join seat b
 on  (case when a.id & 1 then  a.id + 1 = b.id  else a.id -1 = b.id end);