# Description
seat 表存储着座位对应的学生。
+---------+---------+
| id | student |
+---------+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+---------+---------+
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
要求交换相邻座位的两个学生，如果最后一个座位是奇数，那么不交换这个座位上的学生。
+---------+---------+
| id | student |
+---------+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+---------+---------+
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
# Solution
使用多个 union。
## 处理偶数 id，让 id 减 1
## 例如 2,4,6,... 变成 1,3,5,...
SELECT
s1.id - 1 AS id,
s1.student
FROM
seat s1
WHERE
s1.id MOD 2 = 0 UNION
## 处理奇数 id，让 id 加 1。但是如果最大的 id 为奇数，则不做处理
## 例如 1,3,5,... 变成 2,4,6,...
SELECT
s2.id + 1 AS id,
s2.student
FROM
seat s2
WHERE
s2.id MOD 2 = 1
AND s2.id != ( SELECT max( s3.id ) FROM seat s3 ) UNION
## 如果最大的 id 为奇数，单独取出这个数
SELECT
s4.id AS id,
s4.student
FROM
seat s4
WHERE
s4.id MOD 2 = 1
AND s4.id = ( SELECT max( s5.id ) FROM seat s5 )
ORDER BY
id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
# SQL Schema
DROP TABLE
IF
EXISTS seat;
CREATE TABLE seat ( id INT, student VARCHAR ( 255 ) );
INSERT INTO seat ( id, student )
VALUES
( '1', 'Abbot' ),
( '2', 'Doris' ),
( '3', 'Emerson' ),
( '4', 'Green' ),
( '5', 'Jeames' );
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11