Leetcode SQL

626. Exchange Seats

Mary is a teacher in a middle school and she has a table seat storing students' names and their corresponding seat ids.

The column id is continuous increment.

Mary wants to change seats for the adjacent students.

Can you write a SQL query to output the result for Mary?

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

For the sample input, the output is:

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

Note:
If the number of students is odd, there is no need to change the last one’s seat.

Hint: Be careful of the order of “select as cnt from seat”. I sometimes mistakenly put it as “select from seat as cnt”

Solution:

select if(cnt%2=1 and id=cnt, id, if(id%2=1,id+1, id-1)) as id, student from seat,
(select count(*) as cnt from seat) t
order by id;

Link

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store