[SQL]LeetCode180. 连续出现的数字 | Consecutive Numbers
★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
➤微信公众号：山青咏芝（shanqingyongzhi）
➤博客园地址：山青咏芝（https://www.cnblogs.com/strengthen/）
➤GitHub地址：https://github.com/strengthen/LeetCode
➤原文地址：https://www.cnblogs.com/strengthen/p/10152346.html
➤如果链接不是山青咏芝的博客园地址，则可能是爬取作者的文章。
➤原文已修改更新！强烈建议点击原文地址阅读！支持作者！支持原创！
★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
1 Create table If Not Exists Logs (Id int, Num int) 2 Truncate table Logs 3 insert into Logs (Id, Num) values ('1', '1') 4 insert into Logs (Id, Num) values ('2', '1') 5 insert into Logs (Id, Num) values ('3', '1') 6 insert into Logs (Id, Num) values ('4', '2') 7 insert into Logs (Id, Num) values ('5', '1') 8 insert into Logs (Id, Num) values ('6', '2') 9 insert into Logs (Id, Num) values ('7', '2')
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 | +-----------------+
编写一个 SQL 查询，查找所有至少连续出现三次的数字。
+----+-----+ | Id | Num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。
+-----------------+ | ConsecutiveNums | +-----------------+ | 1 | +-----------------+
198ms
1 # Write your MySQL query statement below 2 3 4 # SELECT 5 # DISTINCT l1.Num ConsecutiveNums 6 # FROM 7 # logs l1, 8 # logs l2, 9 # logs l3 10 # WHERE 11 # l1.Id = l2.Id - 1 AND 12 # l2.Id = l3.Id - 1 AND 13 # l1.Num = l2.Num AND 14 # l2.Num = l3.Num 15 16 select DISTINCT num AS "ConsecutiveNums" FROM 17 (select num, 18 case 19 when @record = num then @count:=@count+1 20 when @record <> @record:=num then @count:=1 end as n 21 from 22 Logs ,(select @count:=0,@record:=(SELECT num from Logs limit 0,1)) r 23 ) a 24 where a.n>=3
200ms
1 # Write your MySQL query statement below 2 /* 3 Select DISTINCT l1.Num As ConsecutiveNums from Logs l1, Logs l2, Logs l3 4 where l1.Id = l2.Id - 1 and l2.Id = l3.Id - 1 5 and l1.Num = l2.Num and l2.Num = l3.Num 6 */ 7 select distinct(Num) as ConsecutiveNums 8 from ( 9 select 10 Num, 11 @counter := if(@prev = Num, @counter + 1, 1) as cnt, 12 @prev := Num 13 from Logs y, (select @counter := 1, @prev := NULL) as tmp 14 ) as counts 15 where cnt >= 3;
202ms
1 # Write your MySQL query statement below 2 #select Num as ConsecutiveNums from Logs where 3 4 #select Num, count(Id) as counter from Logs group by Num where counter >= 3 5 6 select distinct Num as ConsecutiveNums from 7 (select 8 Num, 9 @count := if(@prev = (@prev := Num), @count + 1, 1) as counter 10 from 11 Logs, 12 (select @prev := -1, @count := 1) as temp 13 ) as result 14 where counter >= 3;
237ms
1 # Write your MySQL query statement below 2 SELECT distinct num as ConsecutiveNums FROM( 3 SELECT id, num, 4 @pre := @cur, 5 @cur := num, 6 @rep_ct := IF(@pre = @cur, @rep_ct + 1, 1) as rep_ct 7 FROM `Logs` l, (SELECT @pre := null, @cur := 0, @rep_ct := 1) init 8 ) temp WHERE rep_ct >= 3
262ms
1 # Write your MySQL query statement below 2 SELECT x.Num as ConsecutiveNums FROM 3 (SELECT y.Num , SUM(INDEXS) FROM 4 (SELECT Num, 5 CASE WHEN @preNum = t.Num then @index else @index := 1 6 END AS INDEXS, 7 @index := @index+1, 8 @preNum := t.Num 9 FROM Logs t, 10 (SELECT@preNum := null, @index := 1) dse)y 11 WHERE y.INDEXS > 2 12 GROUP BY y.Num)x
