MySQL中改变相邻学生座位_力扣——换座位（数据库的题

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。

其中纵列的 id 是连续递增的

小美想改变相邻俩学生的座位。

你能不能帮她写一个 SQL query 来输出小美想要的结果呢？

示例：

+---------+---------+

| id | student |

+---------+---------+

| 1 | Abbot |

| 2 | Doris |

| 3 | Emerson |

| 4 | Green |

| 5 | Jeames |

+---------+---------+

假如数据输入的是上表，则输出结果如下：

+---------+---------+

| id | student |

+---------+---------+

| 1 | Doris |

| 2 | Abbot |

| 3 | Green |

| 4 | Emerson |

| 5 | Jeames |

+---------+---------+

注意：

如果学生人数是奇数，则不需要改变最后一个同学的座位。

# Write your MySQL query statement below

SELECT * FROM(

SELECT id-1 AS id,student FROM seat WHERE id%2=0

UNION

SELECT id+1 AS id,student FROM seat WHERE id%2=1 AND (id+1) <= (SELECT COUNT(*) FROM seat)

UNION

SELECT id AS id,student FROM seat WHERE id%2=1 AND (id+1) > (SELECT COUNT(*) FROM seat)

) AS T1

ORDER BY id ASC

标签：数据库,seat,力扣,student,query,座位,id,SELECT

来源： https://www.cnblogs.com/JAYPARK/p/10351175.html