09
05

发表于 • • 被 984 人看爆 
小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。

其中纵列的 id 是连续递增的

小美想改变相邻俩学生的座位。

你能不能帮她写一个 SQL query 来输出小美想要的结果呢？

 

示例：
+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Abbot   |
|    2    | Doris   |
|    3    | Emerson |
|    4    | Green   |
|    5    | Jeames  |
+---------+---------+
假如数据输入的是上表，则输出结果如下：

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Doris   |
|    2    | Abbot   |
|    3    | Green   |
|    4    | Emerson |
|    5    | Jeames  |
+---------+---------+
注意：

如果学生人数是奇数，则不需要改变最后一个同学的座位。

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/exchange-seats
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

题解：

方法一：使用CASE THEN和mod()函数

要交换学生座位，我们可以不用根据id交换student，可以直接交换id，将id为奇数的id+1，id为偶数的id-1，最后一个为奇数的不变，然后再排序。
counts目的是得出总人数，判断最后一位

SELECT
    (CASE
    WHEN mod(id,2) != 0 and id != counts THEN id + 1
    WHEN mod(id,2) != 0 and id = counts THEN id
    ELSE id - 1
    END) as id,student
FROM
    seat,
    (select count(*) as counts from seat) seat_count
order by id

方法二：使用位运算异或和COALESCE()函数

奇数^1会减一，偶数^1会加一，所以使用 (id+1)^1-1 计算交换后每个学生的座位 id。

SELECT id, (id+1)^1-1, student FROM seat;

| id | (id+1)^1-1 | student |
|----|------------|---------|
| 1  | 2          | Abbot   |
| 2  | 1          | Doris   |
| 3  | 4          | Emerson |
| 4  | 3          | Green   |
| 5  | 6          | Jeames  |

然后连接原来的座位表和更新 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  |    |         |
前两列来自表 s1，后两列来自表 s2。

最后输出 s1.id 和 s2.student。但是 id=5 的学生，s1.student 正确，s2.student 为 NULL。因此使用 COALESCE() 函数为最后一行记录生成正确的输出。

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;

--我们也可以使用类似方法一的方式来得出结果，但是这样就太麻烦了也不好理解,还不如直接用方法一

SELECT
    (CASE
    WHEN id = counts AND mod(id,2) != 0 THEN id
    ELSE (id+1)^1 - 1
    END
    )as id,student
FROM
    seat,
    (select count(*) as counts from seat) as seat_counts
order by id
分享到：