LeetCode 626. Exchange Seats

題目

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.

題目大意

瑪麗編寫一個查詢以改變鄰近學生的座位,如果學生人數是奇數，則不需要改變最後一個座位。

解題思路

與其變換名稱,不如將id為2n變換為2n-1,2n-1變換為2n (n=1,2,3....)
最好講結果分成兩部分,一部分處理奇數id的變換 一部分處理偶數id的變化
若不考慮邊界情況,則易得sql如下

SELECT tmp.id, tmp.student FROM
(
    SELECT id-1 AS id, student FROM seat WHERE id%2 = 0  -- 偶數 id -1
    UNION
    SELECT id+1 AS id, student FROM seat WHERE id%2 = 1  -- 奇數 id +1
    
) tmp
ORDER BY tmp.id 

考慮邊界則要在update中去除一個id,這個id必須滿足是奇數並且是最大的id

則得sql

SELECT tmp.id, tmp.student FROM
(
    SELECT id-1 AS id, student FROM seat WHERE id%2 = 0  -- 偶數 id -1
    UNION
    SELECT CASE 
        WHEN id = max_id  AND max_id%2 = 1 THEN id 
        ELSE id + 1 END -- 使用END 結束case
            AS id, 
    student 
    FROM seat,(SELECT max(id) as max_id FROM seat) AS t_max_id -- 這裡實際上seat表和一個只有一個元組的seat最大臨時表做了笛卡兒積 
    WHERE id%2 = 1  -- 奇數 id +1
) tmp
ORDER BY tmp.id 

這裡值得注意的是,若你是使用end case 結束case則會報錯

而實際上 MySQL 5.7 規定的標準格式為CASE...END CASE

詳見MySQL 5.7 Reference Manual - 13.6.5.1 CASE Syntax

然後特意找了一臺5.7的資料庫伺服器上重新執行了一下,結果竟然還是報錯了

並且發在網上查閱資料的時候只見到了case... end 的寫法 沒有case... end case

難道是隻在begin ... end 內才這麼寫 case end 用於區分 不同的結束標誌嗎?
詳細原因還需要再繼續學習,若你有正確的認識或想法也請留言或聯絡我