MySQL之LeetCode-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 |
±--------±--------+

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')

解题语句

SELECT
  CASE
    WHEN MOD(id, 2) = 0
    THEN id - 1
    ELSE IF(id = t.maxID, id, id + 1)
  END AS id,
  student
FROM
  seat,
  (SELECT
    MAX(id) AS maxID
  FROM
    seat) AS t
ORDER BY id

高赞评论

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;

SELECT * FROM(
    SELECT id-1 AS id,student FROM seat WHERE id%2=0
    UNION
    SELECT id+1 AS id,student FROM seat WHERE id%2=1 AND (id+1) <= (SELECT COUNT(*) FROM seat)
    UNION
    SELECT id AS id,student FROM seat WHERE id%2=1 AND (id+1) > (SELECT COUNT(*) FROM seat)
) AS T1
ORDER BY id ASC
##########################################
select * from ( 
select id-1 as id,student from seat where id%2 = 0
 #当id数是偶数时，将id数减一,学生坐到前一个人位子上。 
union 
select id+1 as id,student from seat where id%2 = 1 and (id+1) <= (select count(*)from seat) 
#当id数是奇数且不大于且id数加一不大于总座位数时，将id数加一，学生坐到后一个人位子上。 
union 
select id as id, student from seat where id%2=1 and (id+1)>(select count(*)from seat ) 
#当id数是奇数且id数加一大于总座位数时，id数不变。没有多余位子换了，这个学生不动。 
)as T1
 order by id ASC;