LeetCode_OJ【180】Consecutive Numbers

来源：互联网发布：淘宝活动招商入口在哪编辑：程序博客网时间：2024/06/01 08:38

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 |

| 2 | 2 | 2 |

| 1 | 3 | 1 |

| 2 | 4 | 2 |

+------+------+--------------+

然后合并rank，找出count(rank)>=3的num值就行了

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) lgroup 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) lwhere count>=3;

最后吐槽下leetcode，做DB的题目经常第一次提交超时，第二次就通过，查看详情性能还超过了一大半的人，有点无语。。。

0 0