换座位

题目描述

sql架构

Create table If Not Exists seat(id int, student varchar(255))
Truncate table seat
insert into seat (id, student) values ('1', 'Abbot')
insert into seat (id, student) values ('2', 'Doris')
insert into seat (id, student) values ('3', 'Emerson')
insert into seat (id, student) values ('4', 'Green')
insert into seat (id, student) values ('5', 'Jeames')

小美是一所中学的信息科技老师，她有一张 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  |
+---------+---------+

注意：

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

解法1

——使用位操作和coalesce()

算法

使用（id+1）^1-1 计算交换后每个学生的座位id

select
	id,
	(id+1)^1-1,
	staudent
from
	seat;

然后连接原来的座位表和更新id后的座位表

select
	*
from
	seat s1
left join
	seat s2
on
	(s1.id+1)^1-1 = s2.id
order by
	s1.id;

**注：**前两列来自表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;

解法2

使用case

算法
对于所有座位id是奇数的学生，修改其id为id+1，如果最后一个座位id也是奇数，则最后一个座位id不修改，对于所有座位id是偶数的学生，修改其id为id-1

首先查询座位的数量

select
	count(*)
as
	counts
from
	seat

然后使用 case 条件和 mod 函数修改每个学生的位置id

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;