180608_exchange_data
2018-06-08 本文已影响0人
郑磊_4135
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.
SELECT if(id < (select count(*) from seat), if(id mod 2 = 0, id - 1, id + 1), if(id mod 2 = 0, id - 1, id)) as id, student
FROM seat
ORDER BY id ASC
