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则会报错