LeetCode（数据库）- 换座位

题目链接：点击打开链接

 

题目大意：略。

 

解题思路：

• 解决方案(1)：自定义变量，id 保持不变，交换 student，然后最后一个需要特判下是否超过 MAX(id) 即可
• 解决方案(2)：student 保持不变，交换 id，然后最后按照 id 排序下即可
• 解决方案(3)：骚操作，位运算（异或）+ COALESCE 函数（返回其参数中第一个非空表达式）巧妙地解决了特判最后一个问题，主要是理解 ((s1.id + 1) ^ 1) - 1 = s2.id，拿纸笔画下找下规律就知道了，如下所示

  SELECT
      *
  FROM
      seat s1
          LEFT JOIN
      seat s2 ON (s1.id+1)^1-1 = s2.id
  ORDER BY s1.id;
  
  | id | student | id | student |
  |----|---------|----|---------|
  | 1  | Abbot   | 2  | Doris   |
  | 2  | Doris   | 1  | Abbot   |
  | 3  | Emerson | 4  | Green   |
  | 4  | Green   | 3  | Emerson |
  | 5  | Jeames  |    |         |

 

AC 代码

-- 解决方案(1)
SELECT rs.id, rs.stu student
FROM (SELECT id, @tid:=IF(MOD(id, 2) = 1, IF(id + 1 > init.MAXID, id, id + 1), id - 1),  (SELECT student FROM seat WHERE id = @tid) stu
FROM seat, (SELECT @tid:=null, MAX(id) MAXID FROM seat) init) rs

-- 解决方案(2)
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;

-- 解决方案(3)
SELECT
    s1.id, COALESCE(s2.student, s1.student) AS student
FROM
    seat s1
        LEFT JOIN
    seat s2 ON ((s1.id + 1) ^ 1) - 1 = s2.id
ORDER BY s1.id;