https://leetcode.com/problems/exchange-seats/description/
From the discussion section of LeetCode:
select
if(id < (select count(*) from seat), if(id mod 2=0, id-1, id+1), if(id mod 2=0, id-1, id)) as id, student
from seat
order by id;
--------
/* get all the even numbered rows as odd numbered rows */
SELECT s1.id - 1 as id, s1.student
FROM Seat s1
WHERE s1.id MOD 2 = 0
UNION
/* get all the odd numbered rows as even numbered rows */
SELECT s2.id + 1 as id, s2.student
FROM Seat s2
WHERE s2.id MOD 2 = 1 AND s2.id != (SELECT MAX(id) FROM Seat)
/* Just don't get the last row as we will handle it in the next UNION */
UNION
/* get the last row if odd and don't change the id value */
SELECT s3.id, s3.student
FROM Seat s3
WHERE s3.id MOD 2 = 1 AND s3.id = (SELECT MAX(id) FROM Seat)
/* Order the result by id */
ORDER BY id ASC;
-------
select id,
case
when id%2 = 0 then (select student from seat where id = (i.id-1) )
when id%2 != 0 and id<(select count(student) from seat) then (select student from seat where id = (i.id+1) )
else student
end as student
from seat i
No comments:
Post a Comment