[LeetCode] 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.

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

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

+---------+---------+
|    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.

Solution:

select (case
      when (id%2)!=0 and id<>counts then id+1
      when (id%2)!=0 and id = counts then id
      else id-1 end)as id,student
      from seat,(select count(*)as counts from seat)as seat_counts
      order by id;

Runtime: 257 ms, faster than 88.84% of MySQL online submissions for Exchange Seats.

Memory Usage: 0B, less than 100.00% of MySQL online submissions for Exchange Seats.