Mary is a teacher in a middle school and she has a table seat
storing students’ names and their corresponding seat ids.
+---------+---------+ | 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.
- solution 1:对照上表及其查询结果可以得知,当原id为奇数时,交换座位后的id变为id+1,当原id为偶数时,交换座位后的id变为id-1,另一个方面需要考虑的是,学生人数为奇数时,最后一个学生的id不变,故应当用子查询确定学生的人数,然后分情况讨论即可。
- # Write your MySQL query statement below
- 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 s order by id asc
# Write your MySQL query statement below
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 s order by id asc
- SELECT
- s.id,
- s.student
- FROM
- (SELECT id - 1 AS id, student FROM seat WHERE id%2 = 0
- UNION
- SELECT
- (CASE WHEN (cnt%2=1) AND id=cnt THEN id ELSE id + 1 END) AS id, student
- FROM seat, (select count(*) as cnt from seat) as seatcnt
- WHERE id%2 = 1) s
- GROUP BY
- s.id ASC
SELECT
s.id,
s.student
FROM
(SELECT id - 1 AS id, student FROM seat WHERE id%2 = 0
UNION
SELECT
(CASE WHEN (cnt%2=1) AND id=cnt THEN id ELSE id + 1 END) AS id, student
FROM seat, (select count(*) as cnt from seat) as seatcnt
WHERE id%2 = 1) s
GROUP BY
s.id ASC