Exchange Seats

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?

Example

±--------±--------+
| 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 |
±--------±--------+

Solution

MAX(id)比COUNT(*)的执行效率更高，是索引的功劳。
另：此处必须要select as，否则显示将出现问题
# Write your MySQL query statement below
SELECT IF(id%2<>0, IF(id<>(SELECT MAX(id) FROM seat), id+1, id), id-1) id,student 
  FROM seat
 ORDER BY id