LeetCode 626. Exchange Seats SQL Solution

Problem

LeetCode SQL Problem

626. Exchange Seats

student table

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

Solution

MySQL

Exchange Seats

-- Use Left Self Join to out previous student and next student's name in the same row
-- Exchange seat for all students except the last student if he/she does not have next neighbour
SELECT C.id
	,CASE
		WHEN C.id % 2 = 1
			THEN CASE
					WHEN N.id IS NULL
						THEN C.student
					ELSE N.student
					END
		ELSE P.student
		END AS student
FROM student AS C
LEFT JOIN student AS N ON C.id + 1 = N.id
LEFT JOIN student AS P ON P.id + 1 = C.id
ORDER BY C.id;

TSQL

Exchange Seats

-- Use Left Self Join to out previous student and next student's name in the same row
-- Exchange seat for all students except the last student if he/she does not have next neighbour
SELECT C.id
	,CASE
		WHEN C.id % 2 = 1
			THEN CASE
					WHEN N.id IS NULL
						THEN C.student
					ELSE N.student
					END
		ELSE P.student
		END AS student
FROM student AS C
LEFT JOIN student AS N ON C.id + 1 = N.id
LEFT JOIN student AS P ON P.id + 1 = C.id
ORDER BY C.id;

Query Output

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