一、题目
小美是一所中学的信息科技老师,她有一张 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
著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
二、解题思路
1.WHEN CASE
(1) 进行判断,
如果是奇数,id+1
如果是偶数,id-1
如果最后一个id是奇数,则不需要更改其id
case
WHEN mod(seat.id,2)=1 and seat.id!=seatcount.sumseat
THEN seat.id+1
WHEN mod(seat.id,2)=1 and seat.id=seatcount.sumseat
THEN id
ELSE seat.id-1
END
(2)查找最后一个同学id
SELECT count(*) sumseat
FROM seat
(3)具体代码
SELECT
(case
WHEN mod(seat.id,2)=1 and seat.id!=seatcount.sumseat
THEN seat.id+1
WHEN mod(seat.id,2)=1 and seat.id=seatcount.sumseat
THEN id
ELSE seat.id-1
END) id,
student
FROM seat,(
SELECT count(*) sumseat
FROM seat
) as seatcount
ORDER BY id
注意(1):不能用seat.id=seat.id+1
(2)不能只使用一个表
SELECT
(CASE
WHEN mod(id,2)=1 and id!=countnum
THEN id+1
WHEN mod(id,2)=1 and id=countnum
THEN id
ELSE
id-1
END )as id,
student
FROM
(
SELECT *,count(*) countnum
FROM seat
) as s
# s表中结果,与预想的不一致
#{"headers": ["id", "student", "countnum"], "values": [[1, "Abbot", 5]]}
ORDER BY id;
2.if语句
if(表达式1,表达式2,表达式3)
if(表达式1,表达式2,if(表达式3,表达式4,表达式5))
如果表达式1为TRUE(不为0,也不为空),则返回表达式2
否则,返回表达式3
SELECT
if(id%2=1,if(id=(SELECT count(*) FROM seat),id,id+1),id-1) as id,
student
FROM
seat
ORDER BY id ;