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;
谢谢你的观看!