【sql】交换位置 Exchange Seats

最新推荐文章于 2020-12-18 16:38:54 发布

weixin_34278190

最新推荐文章于 2020-12-18 16:38:54 发布

阅读量201

点赞数

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/liyurong/blog/1605918

本文介绍了一个有趣的SQL挑战：为相邻的学生交换座位，并提供了一个具体的解决方案。该方案使用了SQL的UNION和CASE WHEN语句来实现学生座位的有效调整。

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

为什么80%的码农都做不了架构师？>>>

问题：

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.

解决：

① 364ms

SELECT
s.id,
s.student
FROM
(
SELECT
id - 1 AS id,
student
FROM
seat
WHERE
(id % 2 = 0)
UNION
SELECT
(CASE WHEN (cnt%2=1) AND id=cnt THEN id ELSE id + 1 END) AS id,
student
FROM
seat,
(select count(*) as cnt from seat) as seatcnt
WHERE
(id % 2 = 1)
) s
GROUP BY
s.id ASC;