[코드카타] SQL 53 Exchange Seats

[코드카타] SQL 53 Exchange Seats

53. Exchange Seats
https://leetcode.com/problems/exchange-seats/

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.

SELECT id, CASE
        WHEN id % 2 = 0 THEN LAG(student) OVER(ORDER BY id)
        ELSE ifnull(LEAD(student) OVER(ORDER BY id), student)
    END AS student
FROM Seat

홀수와 짝수 구하기 : % or Mod()함수 활용
홀수와 짝수 구분 후 student 컬럼 또는 id 컬럼을 활용하여 풀이 가능
위 방법은 student 컬럼을 활용한 풀이
아래 방법은 id 컬럼을 활용한 풀이
SELECT IF (id < (SELECT MAX(id) FROM Seat), 
            IF(id % 2 = 0, id - 1, id + 1), 
            IF(id % 2 = 0, id - 1, id)
        ) AS id, student
FROM Seat
ORDER BY id