Mysql数据库刷题3

1、体育馆的人流量

请编写一个查询语句，找出人流量的高峰期。高峰期时，至少连续三行记录中的人流量不少于100。

SELECT DISTINCT S1.*
FROM stadium S1
WHERE S1.people >= 100
AND (SELECT COUNT(*) FROM stadium WHERE id BETWEEN S1.id AND S1.id+2 AND people >= 100)>= 3
OR (SELECT COUNT(*) FROM stadium WHERE id BETWEEN S1.id-2 AND S1.id AND people >= 100)>= 3
OR (SELECT COUNT(*) FROM stadium WHERE id BETWEEN S1.id-1 AND S1.id+1 AND people >= 100)>= 3

2、换座位

小美是一所中学的信息科技老师，他有一张seat座位表，平时用来存储学生名字和与他们相对应的座位id。其中纵列的id是连续递增的。小美想改变相邻俩学生的座位。

思路：改变id

SELECT (CASE WHEN MOD(id,2) = 1 AND (SELECT COUNT(*) FROM seat) THEN id
             WHEN MOD(id,2) = 1 THEN id + 1
             ELSE id - 1 END) AS id, student
FROM seat
ORDER BY id