Exchange Seats Problem
Description
LeetCode Problem 626.
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?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
+---------+---------+
| 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.
MySQL Solution
1
2
3
4
5
6
7
8
9
10
11
12
select
(case
when mod(id, 2) != 0 and counts != id then id + 1
when mod(id, 2) != 0 and counts = id then id
else id - 1
end) as id,
student
from
seat,
(select count(*) as counts
from seat) as seat_counts
order by id
LeetCode Database - Medium
LeetCode 177
LeetCode 178
LeetCode 180
LeetCode 184
LeetCode 534
LeetCode 550
LeetCode 570
LeetCode 574
LeetCode 578
LeetCode 580
LeetCode 585
LeetCode 602
LeetCode 608
LeetCode 612
LeetCode 614
LeetCode 626
LeetCode 1045
LeetCode 1070
LeetCode 1077
LeetCode 1098
LeetCode 1107
LeetCode 1112
LeetCode 1126
More LeetCode Database
MySQL Tutorials