ConsecutiveNumbers
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. 解法一: 从上往下对齐排名,如果相等,则排名相等,不相等则排名加一 代码如下: 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 | +------+------+--------------+ 如上所示,如果一个num的连续排名超过3则符合题意。 select num,count(rank) as count 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 ) t group by rank; +------+-------+ | num | count | +------+-------+ | 1 | 3 | | 2 | 1 | | 1 | 1 | | 2 | 2 | +------+-------+ 得到上述的表之后,再使用having条件选择。 最终结果: select distinct num from( select num,count(rank) as count 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 ) t group by rank having count >= 3; ) tmp; 解法二: 1、从第一条记录搜索,前后相同,count++,不相等,count=1; 2、判断,若rank大于3则符合题意 select distinct num from ( select num,@curRank := @preRank+IF(@preNum = num,1,0),@preRank :=IF(@preNum = num,@curRank,1) as rank,@preNum := num from Logs s,(select @preRank := 1) r,(select @preNum := null) p ) t where rank >= 3;
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。