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;