Query more than three records with the same value in field

The initial table data is as follows:
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 is required to achieve the following results:
A B C
---
1 2 3
1 4 5
1 3 6
1 6 9
1 2 3
1 4 5
1 3 6

The table creation statement is as follows:
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 );
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;

Solution:
This question looks very simple. We can even see at a glance which records are connected to three identical records, but do not be confused by its simple appearance, especially those questions that subconsciously can draw conclusions, which will often cause our thinking to fall into the misunderstanding of ourselves, instead of understanding the problems in the computer execution mode, therefore, the core problem to be solved is to convert our way of thinking into a way that SQL can understand.
Let's take a look at our logic to see if it can be converted to the corresponding SQL operation:

First, compare the previous one with the next one to see if it is the same ---> lead. The Lag Analysis Function can achieve this.
Calculation of the same number ---> the count analysis function can be implemented, but there is a problem here. Although the analysis function compares and generates results one by one, it is based on whether it is the same field value, if the value of this field is 0 or 1, count () over (partition by) will not be followed. Therefore, we need to first pass the comparison result field through sum () over (order by rownum) calculates the sum to generate the fields used for partitioning.
If the count is greater than 3, these records meet our needs.

OK. Let's take a step-by-step test. First, generate the same field:

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

--

  

* The rownum column is used for sorting

Then generate columns for 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



--

  

This is much clearer, and the rest is no longer difficult. Count () over () generates the number of records, and you can retrieve records with the number greater than 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

--