LeetCode MySQL-626. Exchange Seats

    AllenLi
    LeetCode MySQL

    Write an SQL query 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.

    寫一個 SQL 查詢交換兩個連續學生的座位 id。
如果學生人數為奇數，則不交換最後一名學生的 id。
依照 id 進行升冪排序結果。

    Table: Seat

    +-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| student     | varchar |
+-------------+---------+
id is the primary key column for this table.
Each row of this table indicates the name and the ID of a student.
id is a continuous increment.

    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.

    Solution:
    1. 選擇標題 id 為

    - id 是偶數，則 id 順序往上一格。
- id 是奇數，且 id 不是 Seat 表中最大的數值，則 id 的順序往下一格。
- 皆不是，則保持原樣。

    2. 選擇標題 student
    3. 來自 Seat 的 table。
    4. 以 id 做升冪排序(ASC)。

    Code.1:

    SELECT 
    (CASE WHEN MOD(id, 2) = 0 THEN id - 1
        WHEN MOD(id, 2) = 1 AND id != (SELECT MAX(id) FROM Seat) THEN id + 1
    ELSE id
    END) AS id,
	student 
FROM Seat
ORDER BY id;

    newSwapSeat

    Related Posts