[MySQL习题] 交换相邻id的座位 Exchange Seats

最新推荐文章于 2024-05-22 17:30:53 发布

memoryqiu

最新推荐文章于 2024-05-22 17:30:53 发布

阅读量1k

点赞数 2

CC 4.0 BY-SA版权

分类专栏： SQL

本文链接：https://blog.youkuaiyun.com/sinat_25873421/article/details/79113437

SQL 专栏收录该内容

38 篇文章

订阅专栏

这篇博客讲述了如何使用SQL查询帮助Mary这位中学教师交换学生相邻座位。内容包括解题思路和实现代码，重点在于处理奇数和偶数id的情况，确保最后一个奇数id的学生座位不变。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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实现，1与2交换，3与4交换… 最后的Id为奇数时，不交换。
先增加一个临时表，字段有id, student, max_id, 其中max_id列为常量，当
（1）奇数id（且id ！= max_id）交换时，id+1；
（2）偶数id交换时，id-1；
（3）max_Id为奇数时，id不变。

实现代码

#方法一
select
(case
when mod(id, 2) = 1 and id != (select max(id) from seat) then id + 1
when mod(id, 2) = 0 then id -1
else id
end) as id, student
from seat
order by id;

#方法二
select
(case 
    when id % 2 = 1 and id != max_id then id + 1
    when id % 2 = 0 then id - 1
    when id = max_id then id               #该条语句不可省略
end) as id, student
from
(select id, student, (select max(id) from seat) as max_id from seat) a  #a为表的别名
order by id;