Exchange Seats - LeetCode

Exchange Seats 문제

출처 : LeetCode Exchange Seats

Table

Column Name	Type
id	int
student	varchar

id is the primary key (unique value) column for this table.
Each row of this table indicates the name and the ID of a student.
The ID sequence always starts from 1 and increments continuously.

Q.

Write a solution 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 result format is in the following example.

---

질문

1) 학생들의 좌석 번호(id)를 두 명씩 묶어서 서로 바꾸되, 학생 수가 홀수면 마지막 학생은 그대로 둔다.

2) 최종 결과는 id 기준 오름차순으로 출력해야된다.

내 답안 📕

WITH new_table AS (
    SELECT (id-1) AS id
         , student
    FROM Seat
    WHERE id % 2 = 0
    UNION ALL
    SELECT (id+1) AS id
         , student
    FROM Seat
    WHERE id % 2 != 0
    ORDER BY id ASC
)

SELECT COUNT(id) OVER (ORDER BY id) AS id
     , student
FROM new_table AS nt
ORDER BY id ASC;

UNION ALL로 두 개의 테이블을 합치고,
윈도우 함수 사용해서 다시 ID 정렬!

---

다른 풀이

WITH new_table AS(
    SELECT *
         , MAX(id) OVER () AS max_id
    FROM Seat
)

SELECT CASE
            WHEN MOD(id, 2) = 1 AND id != max_id THEN id + 1
            WHEN MOD(id, 2) = 1 AND id = max_id THEN id
            ELSE id - 1
       END AS id
     , student
FROM new_table AS nt
ORDER BY id ASC;