编写一个 SQL 查询,查找所有至少连续出现三次的数字。
+——+——-+
| Id | Num |
+——+——-+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+——+——-+
例如,给定上面的 Logs 表, 1 是唯一连续出现至少三次的数字。
+————————-+
| ConsecutiveNums |
+————————-+
| 1 |
+————————-+
1.解题方法
select Nums from (select id,@cout:=if(@pre=Num,@cout:=@cout+1,1) cout,@pre:=Num Nums from logs a,(select @pre:=Null,@cout:=0 ) b) t where t.cout>=3
利用两个新变量:@pre,@cout
2.利用连续序列差值相等
①找规律,找出这连续起来的数字有什么规律呢,我们会发现连续的数字是相同的数字,但是id有可能不是连续的,我们就需要通过对结果集进行再次编号,让其变成连续的。
原始数据:
②首先我们获取到对每条数据编号从1开始使用row_number()函数使用id来排序既row_number() over(order by id)
结果为:
③然后我们通过另一种方式排序将,这些num值一样的进行排序,然后对其编号同样使用row_bumber()使用num来分组使用id排序 over(partition by num order by id)
结果为:
④通过3、4步骤我们能得到什么呢,两个相减之后我们可以得到,只要是相等的,则相减的值是一样的。而且如果不连续的话相减值也不一样。
结果为:
这样是不是一目了然呢,最后在通过num和orde两个共同分组找到一样的一共有几个,我们就可以找到连续的了。
select ConsecutiveNums from (select id,@cout:=if(@pre=Num,@cout:=@cout+1,1) cout,@pre:=Num ConsecutiveNums from logs a,(select @pre:=Null,@cout:=0 ) b) t where t.cout>=3