题30:
根据下表写写一SQL语句来改变相邻俩学生的座位。
解题思路:
- 对于座位id是奇数的学生将其id改为id+1,如果最后一个座位id也是奇数,则最后一个座位id不修改;
- 对于所有座位id是偶数的学生,修改其id为id-1。
(1)条件判断语句:case
(2)如何判断奇数和偶数:mod函数
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;