**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 | +-----+----------+--+ ```