sql notes: leetcode#626 exchange seats

Problem

Mary is a teacher in a middle school and she has a table seat storing students’ names and their corresponding seat ids.

The column id is continuous increment.
Mary wants to change seats for the adjacent students.
Can you write a SQL query to output the result for Mary?

1
2
3
4
5
6
7
8
9

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Abbot   |
|    2    | Doris   |
|    3    | Emerson |
|    4    | Green   |
|    5    | Jeames  |
+---------+---------+

For the sample input, the output is:

1
2
3
4
5
6
7
8
9

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Doris   |
|    2    | Abbot   |
|    3    | Green   |
|    4    | Emerson |
|    5    | Jeames  |
+---------+---------+

Note:
If the number of students is odd, there is no need to change the last one’s seat.

Analysis

In order to exchange seats, we can exchange id and then order by id. If origin id is odd, add 1 as new id, otherwise minus 1 as new id. But if the table has odd number of records, the id of last record will not change. As a result, we need to count how many records the table has and store it in a temp table first:

1

(SELECT COUNT(*) AS cnt FROM seat) AS t

Then use IF() function to change id. If total number of records in the table is odd and id equals to the total number, do not change id and return origin id directly:

1
2

SELECT IF(cnt % 2 = 1 AND id = cnt, id, IF()) AS id, student FROM seat,
(SELECT COUNT(*) AS cnt FROM seat) AS t

If the table has even number of records or the id is the last one, check whether id is even or odd. If odd, add 1 as new id, otherwise minus 1 as new id:

1
2

SELECT IF(cnt % 2 = 1 AND id = cnt, id, IF(id % 2 = 1, id + 1, id - 1)) AS id, student FROM seat,
(SELECT COUNT(*) AS cnt FROM seat) AS t

Sort by id finally:

1
2
3

SELECT IF(cnt % 2 = 1 AND id = cnt, id, IF(id % 2 = 1, id + 1, id - 1)) AS id, student FROM seat,
(SELECT COUNT(*) AS cnt FROM seat) AS t
ORDER BY id;

Solution

1
2
3

SELECT IF(cnt % 2 = 1 AND id = cnt, id, IF(id % 2 = 1, id + 1, id - 1)) AS id, student FROM seat,
(SELECT COUNT(*) AS cnt FROM seat) AS t
ORDER BY id;

Link

626. Exchange Seats
(中文版) SQL 笔记: Leetcode#626 Exchange Seats