Consecutive Numbers,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.
解法一:
参考[Rank Scores](http://blog.csdn.net/havedream_one/article/details/45395063)
从上往下对齐排名,如果相等,则排名相等,不相等则排名加一
代码如下:
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,我们会在尽快处理。