[626] Exchange Seats

Exchange Seats - LeetCode

Table: Seat

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
id is the primary key column for this table.
Each row of this table indicates the name and the ID of a student.
id is a continuous increment.

 

Write an SQL query to swap the seat id of every two consecutive students. If the number of students is odd, the id of the last student is not swapped.

Return the result table ordered by id in ascending order.

The query result format is in the following example.

 

Example 1:

Input: 
Seat table:
+----+---------+
| id | student |
+----+---------+
| 1  | Abbot   |
| 2  | Doris   |
| 3  | Emerson |
| 4  | Green   |
| 5  | Jeames  |
+----+---------+
Output: 
+----+---------+
| id | student |
+----+---------+
| 1  | Doris   |
| 2  | Abbot   |
| 3  | Green   |
| 4  | Emerson |
| 5  | Jeames  |
+----+---------+
Explanation: 
Note that if the number of students is odd, there is no need to change the last one's seat.

 

문제 조건 

연속된 2명의 학생마다 좌석 ID를 바꾸는 쿼리를 작성하시오.

전체 학생 수가 홀수인 경우 마지막 학생의 좌석 ID는 변경되지 않습니다. 

 

# case when 
SELECT CASE WHEN id % 2 = 0 THEN id-1 -- 짝수면 아래로 이동 
            WHEN id = (SELECT MAX(id) FROM Seat) THEN id -- 맥스면 그 자리 그대로 
            WHEN id % 2 !=0 THEN id + 1 -- 홀수면 위로 이동
        END AS "id"
        , Student
FROM seat 
ORDER BY 1

# case when 간략화 
SELECT CASE WHEN id % 2 = 0 THEN id-1 
            WHEN id % 2 != 0 AND id = (SELECT MAX(id) FROM Seat) THEN id 
        ELSE id +1
        END AS "id"
        , Student
FROM seat 
ORDER BY 1

# window functin 
SELECT ROW_NUMBER() OVER() id, student
FROM seat
ORDER BY IF(MOD(id, 2) = 0, id-1, id+1) -- 왜 5 값이 영향을 받지 않지?

Q. 근데 왜 5 값이 영향을 받지 않는가?

     row_number로 출력을 했다 해도 id 값은 변해야 하는게 아닌가..? 

["idn", "id", "student"], 
[1, 2, "Doris"]
[2, 1, "Abbot"]
[3, 4, "Green"]
[4, 3, "Emerson"]
[5, 5, "Jeames"]