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.
思路:id为奇数则id +1, id为偶数则id-1,且id总数为奇数时,最大id数不变
使用case when语句:
select case when id % 2 = 1 and id != (select count(id)
from seat) then id + 1
when id % 2 = 0 then id - 1
else id
end as id, student
from seat
order by id