心得
這題要找出連續三次重複出現的數字,我原本一直在想如果中間有斷層(中間有資料被刪除)的話是否要先自己排序一次,結果看了一下Top Solutions才發現根本不用考慮這個問題,直接ID+1尋找下筆資料即可,既然這麼單純的話也沒什麼問題了。
問題
Write a SQL query to find all numbers that appear at least three times consecutively.
1234567891011 +----+-----+| 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.
答案
- 方法對,但會TimeOut
123456789# Write your MySQL query statement belowSELECT DISTINCT(a.Num) AS ConsecutiveNumsFROM Logs AS aWHERE ( SELECT z.NumFROM Logs AS zWHERE z.Id = a.Id + 1) = a.NumAND ( SELECT z.NumFROM Logs AS zWHERE z.Id = a.Id + 2) = a.Num - 通過
123456789# Write your MySQL query statement belowSELECT DISTINCT(a.Num) AS ConsecutiveNumsFROM Logs AS aJOIN Logs AS bON a.Id = b.Id - 1JOIN Logs AS cON b.Id = c.Id - 1WHERE a.Num = b.NumAND b.Num = c.Num