Write a SQL query to find all numbers that appear at least three times consecutively.
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
題意:求表中連續出現3次以上的資料.
因此，根據題意構造第一版本答案（使用連續的ID進行比較）:
# Write your MySQL query statement below
SELECT DISTINCT t1.Num AS ConsecutiveNums
FROM
Logs t1,
Logs t2,
Logs t3
WHERE
t1.Id = t3.Id - 1
AND t2.Id = t3.Id + 1
AND t1.Num = t2.Num
AND t2.Num = t3.Num;
當前版本答案通過了測驗，但是運行效率太低了.
分析原因，可能與t1.Id = t3.Id - 1條件相關，當t3.Id為0時，-1不會尋找到相關資料，導致sql執行緩慢.
因此，修改為如下所示：
# Write your MySQL query statement below
# Write your MySQL query statement below
SELECT DISTINCT t1.Num AS ConsecutiveNums
FROM
Logs t1,
Logs t2,
Logs t3
WHERE
t2.Id = t1.Id + 1
AND t3.Id = t1.Id + 2
AND t1.Num = t2.Num
AND t2.Num = t3.Num;
此版本，效率得到了巨大的提高，
PS:
如果您覺得我的文章對您有幫助，請關注我的微信公眾號，謝謝!
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/122821.html
標籤：MySQL