SQL> SELECT acc_nbr, serv_id 
 2  FROM (SELECT t2.*, COUNT (1) OVER (PARTITION BY acc_nbr, rn) cnt 
 3      FROM (SELECT t1.*, t1.serv_id - ROWNUM rn 
 4          FROM (select * from serv order by acc_nbr, serv_id)t1) t2) 
 5   WHERE cnt >= 2; 
  
 执行计划 
 ---------------------- 
 Plan hash value: 1564398092 
  
 -------------------------------------------- 
 | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time   | 
 -------------------------------------------- 
 |   0 | SELECT STATEMENT    |    |  13 |   507 |   5  (40)| 00:00:01 | 
 |*  1 |  VIEW           |    |  13 |   507 |   5  (40)| 00:00:01 | 
 |   2 |   WINDOW SORT       |    |  13 |   507 |   5  (40)| 00:00:01 | 
 |   3 |  VIEW         |    |  13 |   507 |   4  (25)| 00:00:01 | 
 |   4 |   COUNT         |    |     |     |      |      | 
 |   5 |    VIEW         |    |  13 |   338 |   4  (25)| 00:00:01 | 
 |   6 |     SORT ORDER BY   |    |  13 |   338 |   4  (25)| 00:00:01 | 
 |   7 |    TABLE ACCESS FULL| SERV |  13 |   338 |   3   (0)| 00:00:01 | 
 -------------------------------------------- 
  
 Predicate Information (identified by operation id): 
 --------------------------------------------------- 
  
  1 - filter("CNT">=2) 
  
 Note 
 ----- 
  - dynamic sampling used for this statement 
  
 SQL> with t as (select * 
 2    from serv 
 3     where acc_nbr in (select distinct a.acc_nbr 
 4               from (select acc_nbr, rownum rn from serv) a, 
 5                (select lag(acc_nbr, 1) over(order by acc_nbr) acc_nbr, 
 6                    rownum rn 
 7                   from serv) b 
 8              where a.rn = b.rn 
 9              and a.acc_nbr = b.acc_nbr) 
  10    order by 1, 2) 
  11    select acc_nbr ,serv_id from t intersect (select acc_nbr,serv_id+1 from t union  select acc 
 _nbr,serv_id-1 from t) 
  12   ; 
  
 执行计划 
 ---------------------- 
 Plan hash value: 3188862905 
  
 ---------------------------------------------------------------- 
 | Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time    
 ---------------------------------------------------------------- 
 |   0 | SELECT STATEMENT       |               |  13 |  1014 |   9  (78)| 00:00:01