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 | +-----------------+
DISTINCT and WHERE clause [Accepted]Algorithm
\nConsecutive appearing means the Id of the Num are next to each others. Since this problem asks for numbers appearing at least three times consecutively, we can use 3 aliases for this table Logs, and then check whether 3 consecutive numbers are all the same.
\nSELECT *\nFROM\n Logs l1,\n Logs l2,\n Logs l3\nWHERE\n l1.Id = l2.Id - 1\n AND l2.Id = l3.Id - 1\n AND l1.Num = l2.Num\n AND l2.Num = l3.Num\n;\n
| Id | \nNum | \nId | \nNum | \nId | \nNum | \n
|---|---|---|---|---|---|
| 1 | \n1 | \n2 | \n1 | \n3 | \n1 | \n
| >Note: The first two columns are from l1, then the next two are from l2, and the last two are from l3. | \n\n | \n | \n | \n | \n |
Then we can select any Num column from the above table to get the target data. However, we need to add a keyword DISTINCT because it will display a duplicated number if one number appears more than 3 times consecutively.
MySQL
\nSELECT DISTINCT\n l1.Num AS ConsecutiveNums\nFROM\n Logs l1,\n Logs l2,\n Logs l3\nWHERE\n l1.Id = l2.Id - 1\n AND l2.Id = l3.Id - 1\n AND l1.Num = l2.Num\n AND l2.Num = l3.Num\n;\n