Exchange Seats

最新推荐文章于 2020-10-09 09:18:23 发布

原创最新推荐文章于 2020-10-09 09:18:23 发布 · 368 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

LeetCode刷题同时被 2 个专栏收录

13 篇文章

订阅专栏

SQL

6 篇文章

订阅专栏

本文介绍了一个有趣的SQL挑战，即在不改变奇数位置末尾学生座位的情况下，将相邻学生的座位进行交换。通过使用CASE WHEN语句或IF函数，我们展示了如何实现这一目标，并提供了两种有效的解决方案。

Exchange Seats

description:
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.

这道题给了一个seat表，包含学生的座位号id和姓名，并且id是连续相邻递增的。
题目让把相邻座位号的学生交换位置，也就是1和2换，3和4换。交换时直接交换学生的姓名。如果总人数是奇数的话，最后一名学生的位置不动。

这道题不好做，看了leetcode官方solution才懂了。要用到case when语句或者if函数。

思路：对于奇数的id，如果是最后一个id，则id不变；否则id=id+1；如果是偶数的id，则id=id-1。id号的总数可以用select Count(*) from seat查出来。

这是官方题解：

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 ASC;

也可以用IF函数，IF(condition, value_if_true, value_if_false)
IF函数类似高级语言的三元表达式，condition为真时返回value_if_true，condition为假时返回value_if_false。
因此也可以这样写：

# Write your MySQL query statement below
select IF(id % 2 = 0, id - 1, 
         IF(id = (select count(*) from seat),
           id, id + 1))
as id, student from seat order by id;

谢谢你的观看！