**1. 查找所有至少连续出现三次的数字** 练习用表： ```sql select * from logs; +----------+-----------+--+ | logs.id | logs.num | +----------+-----------+--+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----------+-----------+--+ ``` 方法一：借助id有序，通过子关联进行查询 ```sql select distinct a.num as ConsecutiveNums from logs a join logs b join logs c on a.num=b.num and b.num=c.num where a.id=b.id-1 and b.id=c.id-1; -- 或者改写如下 select distinct a.num ConsecutiveNums from logs a join logs b on a.num=b.num and a.id=b.id-1 join logs c on b.num=c.num and b.id=c.id-1; +------------------+--+ | consecutivenums | +------------------+--+ | 1 | +------------------+--+ ``` 方法二：使用窗口函数rank() ```sql select distinct(b.ConsecutiveNums) as ConsecutiveNums from( select a.rn, a.num as ConsecutiveNums from( select num, (rank() over(order by id)-rank() over(partition by num order by id)) as rn from logs ) a group by a.rn, a.num having count(1)>2 )b; +------------------+--+ | consecutivenums | +------------------+--+ | 1 | +------------------+--+ ``` 方法三：使用窗口函数 lead()，当然也可以使用 lag() ```sql select distinct num ConsecutiveNums from( select num, lead(num, 1) over() as num1, lead(num, 2) over() as num2 from logs ) as c where c.num=num1 and c.num1=c.num2; +------------------+--+ | consecutivenums | +------------------+--+ | 1 | +------------------+--+ ``` **2. 换座位** 小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。 （1）其中纵列的 id 是连续递增的 （2）小美想改变相邻俩学生的座位。 你能不能帮她写一个 SQL query 来输出小美想要的结果呢？ <br/> 练习用表： ```sql select * from seat; +----------+---------------+--+ | seat.id | seat.student | +----------+---------------+--+ | 1 | Abbot | | 2 | Doris | | 3 | Emerson | | 4 | Green | | 5 | Jeames | +----------+---------------+--+ ``` 方法一： ```sql select id, case when id%2=1 then lead(student, 1, student) over(order by id) when id%2=0 then lag(student, 1) over(order by id) end as student from seat; +-----+----------+--+ | id | student | +-----+----------+--+ | 1 | Doris | | 2 | Abbot | | 3 | Green | | 4 | Emerson | | 5 | Jeames | +-----+----------+--+ ``` 方法二： ```sql -- ^ 异或运算，偶数与1异或则+1，奇数则-1 select rank() over(order by (id-1)^1) as id, student from seat; +-----+----------+--+ | id | student | +-----+----------+--+ | 1 | Doris | | 2 | Abbot | | 3 | Green | | 4 | Emerson | | 5 | Jeames | +-----+----------+--+ ```