阅读之前,请牛刀小试:https://leetcode-cn.com/problems/exchange-seats/
本系列教程,基于19个案例来探索SQL编程的艺术。 SQL 是一门结构化查询语言,更是一门面向集合的语言。
一 、题目:626. 换座位
小美是一所中学的信息科技老师,她有一张 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 |
注意:如果学生人数是奇数,则不需要改变最后一个同学的座位。
应用场景
数据交换的情况比较少,主要是考察自关联的理解,子查询等; 常常需要扩展,升级成批量更新。
二、准备:创建表插入数据
CREATE TABLE seat(
id INT PRIMARY KEY,
student VARCHAR(200)
);
-- TRUNCATE TABLE seat;
INSERT INTO seat(id,student)
VALUES (1,'Abbot'),(2,'Doris'),(3,'Emerson'),(4,'Green'),(5,'Jeames');
三、解法:条条大路
3.1 解法一 : case when 和子查询
SELECT s1.id AS id,
CASE WHEN (id % 2 = 1) THEN IFNULL((SELECT s2.student FROM seat s2 WHERE s2.id = s1.id + 1),s1.student)
ELSE (SELECT s3.student FROM seat s3 WHERE s3.id = s1.id - 1)
END AS student
FROM seat s1
等价写法
SELECT (CASE
WHEN MOD(id,2) = 1 AND id = (SELECT COUNT(*) FROM seat) THEN id
WHEN MOD(id,2) = 1 THEN id+1
ElSE id-1
END) AS id, student
FROM seat
ORDER BY id;
case when 真的很好用;更多案例:https://blog.csdn.net/qq_31156277/article/details/84258867
《sql进阶教程》之用关联子查询比较行与行:https://blog.csdn.net/qq_31156277/article/details/84558374
3.2 扩展:将查询语句变为更新语句
只需要在查询的基础上再嵌套一层就能做更新了。
UPDATE seat s
INNER JOIN (
SELECT s1.id AS id,
CASE WHEN (id % 2 = 1) THEN IFNULL((SELECT s2.student FROM seat s2 WHERE s2.id = s1.id + 1),s1.student)
ELSE (SELECT s3.student FROM seat s3 WHERE s3.id = s1.id - 1)
END AS student
FROM seat s1
) tmp ON s.id = tmp.id
SET s.student = tmp.student;
-- SELECT * FROM seat;