题目来源:https://leetcode.com/problems/consecutive-numbers/
输出连续三次同样的数字(Id连续,Num相同)
一开始当然还是想用之前的做法,连着join三次:
select distinct a.Num
from Logs a join Logs b join Logs c
on a.Id+2 = b.Id+1 = c.Id and a.Num = b.Num = c.Num
果然超时了,第一次做数据库的题也TLE。。
然后意识到没有必要三重循环的,二重的就够了,先找到对应的b,然后再去找对应的c,复杂度降了一维:
select distinct a.Num
from Logs a
join Logs b on a.Id+1 = b.Id and a.Num = b.Num
join Logs c on a.Id+2 = c.Id and a.Num = c.Num
通过了,时间是1669 ms,不大满意这个结果。毕竟刚刚做过连续两天天气的问题,现在又出现连续三天的问题,如何从暴力解决改进到简便快捷方法呢?
上网之后查到一个解法,先记下来以后研究:
select DISTINCT num
FROM (
select
num,
case when @record = num then @count:=@count+1
when @record <> @record:=num then @count:=1
end as n
from Logs ,(
select
@count:=0,
@record:=(SELECT num from Logs limit 0,1)
) r
) a
where a.n>=3
速度依然很慢。。1680 ms 。。