LeetCode 0626. Exchange Seats SQL Solution | Explanation + Code

Description

Table: Seat

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| student     | varchar |
+-------------+---------+
id is the primary key (unique value) column for this table.
Each row of this table indicates the name and the ID of a student.
The ID sequence always starts from 1 and increments continuously.

 

Write a solution to swap the seat id of every two consecutive students. If the number of students is odd, the id of the last student is not swapped.

Return the result table ordered by id in ascending order.

The result format is in the following example.

 

Example 1:

Input: 
Seat table:
+----+---------+
| id | student |
+----+---------+
| 1  | Abbot   |
| 2  | Doris   |
| 3  | Emerson |
| 4  | Green   |
| 5  | Jeames  |
+----+---------+
Output: 
+----+---------+
| id | student |
+----+---------+
| 1  | Doris   |
| 2  | Abbot   |
| 3  | Green   |
| 4  | Emerson |
| 5  | Jeames  |
+----+---------+
Explanation: 
Note that if the number of students is odd, there is no need to change the last one's seat.

Solutions

Solution 1

sql

# Write your MySQL query statement below SELECT s1.id, COALESCE(s2.student, s1.student) AS student FROM Seat AS s1 LEFT JOIN Seat AS s2 ON (s1.id + 1) ^ 1 - 1 = s2.id ORDER BY 1;(code-box)

Solution 2

sql

# Write your MySQL query statement below SELECT id + ( CASE WHEN id % 2 = 1 AND id != (SELECT MAX(id) FROM Seat) THEN 1 WHEN id % 2 = 0 THEN -1 ELSE 0 END ) AS id, student FROM Seat ORDER BY 1;(code-box)

Solution 3

sql

# Write your MySQL query statement below SELECT RANK() OVER (ORDER BY (id - 1) ^ 1) AS id, student FROM Seat;(code-box)

Solution 4

sql

# Write your MySQL query statement below SELECT CASE WHEN id & 1 = 0 THEN id - 1 WHEN ROW_NUMBER() OVER (ORDER BY id) != COUNT(id) OVER () THEN id + 1 ELSE id END AS id, student FROM Seat ORDER BY 1;(code-box)