SQL相邻学生座位互换CASE WHEN实现及其他简便解法咨询

更简洁的CASE语句实现 #

你原来的写法需要重复查询max(id)，可以用窗口函数LEAD/LAG简化逻辑，不需要单独判断最后一个奇数id的特殊情况，可读性和执行效率都更高：

SELECT
    id,
    CASE
        WHEN MOD(id, 2) = 1 THEN LEAD(student, 1, student) OVER(ORDER BY id)
        ELSE LAG(student) OVER(ORDER BY id)
    END AS student
FROM seat
ORDER BY id

逻辑说明：

• 奇数id的行，取下一行的学生名，如果是最后一行没有下一行，就默认取当前行的学生名
• 偶数id的行，取上一行的学生名
• 不需要修改原id字段，省去了排序前重新算id的步骤

如果你用的数据库不支持窗口函数，可以用CTE提前取出最大id，避免重复子查询：

WITH max_id_info AS (SELECT MAX(id) AS max_id FROM seat)
SELECT
    CASE
        WHEN id = max_id AND MOD(max_id, 2) = 1 THEN id
        WHEN MOD(id, 2) = 1 THEN id + 1
        ELSE id - 1
    END AS id,
    student
FROM seat, max_id_info
ORDER BY id

其他实现思路 #

1. 自连接实现 #

不用CASE语句，通过左连接匹配相邻行的学生信息：

SELECT
    s1.id,
    COALESCE(s2.student, s1.student) AS student
FROM seat s1
LEFT JOIN seat s2 
ON (s1.id % 2 = 1 AND s1.id + 1 = s2.id) 
OR (s1.id % 2 = 0 AND s1.id - 1 = s2.id)
ORDER BY s1.id

2. 排序偏移重编号实现 #

通过对奇偶id做偏移排序，再用窗口函数重新生成id，逻辑非常简洁：

SELECT
    ROW_NUMBER() OVER(ORDER BY IF(MOD(id,2)=1, id+0.1, id-0.1)) AS id,
    student
FROM seat

逻辑说明：给奇数id加0.1，偶数id减0.1，排序后相邻的奇偶id会交换顺序，重新编号后直接得到目标id。

3. IF函数简化（适用于MySQL等支持IF函数的数据库） #

如果不限制必须用CASE，用IF函数可以把代码压得更短：

SELECT
    IF(MOD(id,2)=1 AND id=(SELECT MAX(id) FROM seat), id, IF(MOD(id,2)=1, id+1, id-1)) AS id,
    student
FROM seat
ORDER BY id

内容的提问来源于stack exchange，提问作者vegetarianCoder