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.
在刚开始做这道题目的时候完全没有接触过sql中的变量,也就能写出下面这种sql语句
select distinct num from Logs l1 where EXISTS (select id from Logs l3 where l3.id = l1.id +2 and l1.num = l3.num)
and EXISTS (select id from Logs l2 where l2.id = l1.id +1 and l1.num = l2.num);
这种方法在数据量比较小的时候也能得出满意的结果,但是数据量一多直接超时,毕竟对于每条记录都进行多表连接操作太费时费力了。
然后在网上搜了下找到了下面这个方法:
http://www.2cto.com/database/201505/396743.html
方法一:
增加一列rank,初始值为1,从上往下扫描Logs表的时候,碰到num值相同的元组,设置同样的rank值,否则rank值+1;
代码如下:
select num,@curRank := @curRank+IF(@preNum = num,0,1) as rank ,@preNum:=num from logs s,(select @curRank := 0) r,(select @preNum := null) p;
+
------+------+--------------+
| num | rank | @preNum:=num |
+
------+------+--------------+
| 1 | 1 | 1 |
| 1 | 1 | 1 |
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 1 | 3 | 1 |
| 2 | 4 | 2 |
| 2 | 4 | 2 |
+
------+------+--------------+
select num from
(select num,@curRank := @curRank+IF(@preNum = num,0,1) as rank ,@preNum:=num from Logs s,(select @curRank := 0) r,(select @preNum := null) p) l
group by rank having count(rank) >= 3;
方法二:
增加count列,碰到相同num则count+1否则count=1,最后返回count>=3的num值。
select distinct num from (
select num,@count:=if(@preNum = num,@count+1,1) as count,@preNum:=num
from Logs,(select @count:=1) c,(select @preNum:= null) p
) l
where count>=3;
最后吐槽下leetcode,做DB的题目经常第一次提交超时,第二次就通过,查看详情性能还超过了一大半的人,有点无语。。。