Welcome to Subscribe On Youtube
Formatted question description: https://leetcode.ca/all/626.html
626. Exchange Seats
Level
Medium
Description
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.
Solution
Change each row’s id, and sort the entries according to id in ascending order.
Use case statement to change the values of id. If id % 2 = 0, then change it to id - 1. Else, if id is the maximum id, then remain it unchanged. Else, change it to id + 1.
# Write your MySQL query statement below
select (
case
when id % 2 = 0 then id - 1
when id = (select max(id) from seat) then id
else id + 1
end
) as id, student
from seat
order by id;