how to find the number has more than two consecutive appearences?

Question

The source table:

id     num
-------------------
1       1
2       1
3       1
4       2
5       2
6       1

The output:(appear at least 2 times)

num   times
--------------
1      3
2      2

Answer 1

Based on the addition logic defined in the comments it appears this is what you're after:

WITH YourTable AS(
    SELECT V.id,
           V.num
    FROM (VALUES(1,1),
                (2,1),
                (3,1),
                (4,2),
                (5,2),
                (6,1),
                (7,1))V(id,num)), --Added extra row due to logic defined in comments
Grps AS(
    SELECT YT.id,
           YT.num,
           ROW_NUMBER() OVER (ORDER BY id) -
           ROW_NUMBER() OVER (PARTITION BY Num ORDER BY id) AS Grp
    FROM YourTable YT),
Counts AS(
    SELECT num,
           COUNT(num) AS Times
    FROM grps
    GROUP BY grp,
             num)
SELECT num,
       MAX(times) AS times
FROM Counts
GROUP BY num;

This uses a CTE and ROW_NUMBER to define the groups, and then an additional CTE to get the COUNT per group. Finally you can then get the MAX COUNT per num.