查询字段a的值连续三条以上相同的记录

初始表数据如下：
a b c
- - -
1 2 3
1 4 5
1 3 6
2 3 3
1 5 7
2 5 8
1 6 9
1 2 3
1 4 5
1 3 6

要求用SQL实现如下效果：
a b c
- - -
1 2 3
1 4 5
1 3 6
1 6 9
1 2 3
1 4 5
1 3 6

建表语句如下：
create table tmp2 (a number,b number, c number);
insert into tmp2 values (1,2,3);
insert into tmp2 values (1,4,5);
insert into tmp2 values (1,3,6);
insert into tmp2 values (2,3,3);
insert into tmp2 values (1,5,7);
insert into tmp2 values (2,5,8);
insert into tmp2 values (1,6,9);
insert into tmp2 values (1,2,3);
insert into tmp2 values (1,4,5);
insert into tmp2 values (1,3,6);
commit;

解题思路：
这道题看起来非常简单，我们甚至一眼就能看出来哪些记录是连接3条相同的，但千万不要被其简单的表象迷惑了，特别是那些下意识就能得出结论的问题，这往往会让我们的思维陷入到自我的思维误区中，而不再以计算机的执行模式去理解问题，因此这题核心要解决的问题就是将我们的思维方式转换成sql可以理解的记数方式。
先来理一理我们的逻辑，看看能否转换成对应的SQL操作：

首先肯定是拿上一条与下一条做对比，看看是否相同--->lead,lag分析函数可以实现这一点
计算相同数--->count分析函数可以实现，但是这里面有一个问题，分析函数虽然是逐条对比生成结果，但此处我们的依据是是否相同的字段值，假设该字段值为0或1的话，count() over(partition by )就没有了依照，因此我们需要先将比较的结果字段通过sum() over(order by rownum)计算相加，以便生成分区用的字段。
如果计数>3则这些记录符合我们的需求

OK，思路理清了，下面一步步来试试，首先生成比较是否相同的字段：

PHP code:
  
  



JSSWEB> select a.*,


     2         rownum rn,


     3         decode(a, lag(a, 1, a) over(order by rownum), 0, 1) na


     4    from tmp2 a


     5  ;


 


         A          B          C         RN         NA


---------- ---------- ---------- ---------- ----------


         1          2          3          1          0


         1          4          5          2          0


         1          3          6          3          0


         2          3          3          4          1


         1          5          7          5          1


         2          5          8          6          1


         1          6          9          7          1


         1          2          3          8          0


         1          4          5          9          0


         1          3          6         10          0


 


10 rows selected


--

  
  

*rownum列是为了排序用

然后生成用于partition的列

PHP code:
  
  



JSSWEB> select b.*, sum(na) over(order by rn) so


     2    from (select a.*,


     3                 rownum rn,


     4                 decode(a, lag(a, 1, a) over(order by rownum), 0, 1) na


     5            from tmp2 a) b


     6  ;


 


         A          B          C         RN         NA         SO


---------- ---------- ---------- ---------- ---------- ----------


         1          2          3          1          0          0


         1          4          5          2          0          0


         1          3          6          3          0          0


         2          3          3          4          1          1


         1          5          7          5          1          2


         2          5          8          6          1          3


         1          6          9          7          1          4


         1          2          3          8          0          4


         1          4          5          9          0          4


         1          3          6         10          0          4


 


10 rows selected





--

  
  

这下就清晰多了，剩下的就没难度了，count() over()生成数量，取数量大于2的记录即可：

PHP code:

JSSWEB> select a,b,c from(

     2  select c.*, count(so) over(partition by so) ct

     3    from (select b.*, sum(na) over(order by rn) so

     4            from (select a.*,

     5                         rownum rn,

     6                         decode(a, lag(a, 1, a) over(order by rownum), 0, 1) na

     7                    from tmp2 a) b) c

     8  )where ct>=3

     9  ;

 

         A          B          C

---------- ---------- ----------

         1          2          3

         1          4          5

         1          3          6

         1          6          9

         1          2          3

         1          4          5

         1          3          6

 

7 rows selected

--

---