Puzzle is taken from https://oj.leetcode.com/problems/consecutive-numbers/
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.
The solution is to join the table to itself three times and make sure the `Id` field is consecutive but the `Num` is the same.
select Distinct `A`.`Num` from `Logs` as `A`, `Logs` as `B`, `Logs` as `C` where `A`.`Id` = `B`.`Id` - 1 and `B`.`Id` = `C`.`Id` - 1 and `A`.`Num` = `B`.`Num` and `B`.`Num` = `C`.`Num`
You can rewrite it using inner join:
select distinct(Logs.Num) as triplets from Logs inner join (select logs1.Id as id1, logs2.id as id2, logs1.Num as Num from Logs as logs1 inner join Logs as logs2 on logs1.Id = logs2.Id - 1 where logs1.Num = logs2.Num) as doubles on Logs.Id = doubles.id1 + 2 where doubles.Num = Logs.Num;
–EOF (The Ultimate Computing & Technology Blog) —
GD Star Rating
loading...
248 wordsloading...
Last Post: How to Make a Virtual Drive on Windows Based on a Directory Using SubSt Command ?
Next Post: USB Soft Keyboard Review, Not So Much Useful