Consecutive Numbers
来源:程序员人生 发布时间:2015-08-19 08:28:07 阅读次数:3079次
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.
解法1:
参考[Rank Scores](http://blog.csdn.net/havedream_one/article/details/45395063)
从上往下对齐排名,如果相等,则排名相等,不相等则排名加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 |
+------+------+--------------+
如上所示,如果1个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;
解法2:
1、从第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;
生活不易,码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠