求助高手帮忙写个SQL------急急急！！！
用一个sql实现以下功能：
serv表，有acc_nbr和serv_id两个字段，其中有些相同的acc_nbr的serv_id是连续的，需要把这些acc_nbr和连续的serv_id找出来。
如
acc_nbr serv_id
11
12
13
14
16
22
23
31
42
51
54
55
要求取出的结果是
acc_nbr serv_id
11
12
13
14
22
23
54
55


[解决办法]

SQL code

 
 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   
 
 |   1 |  TEMP TABLE TRANSFORMATION |               |     |     |      |       
 |   2 |   LOAD AS SELECT       |               |     |     |      |       
 |   3 |  SORT ORDER BY       |               |   3 |   117 |  12  (25)| 00:00:01  
 |*  4 |   HASH JOIN SEMI     |               |   3 |   117 |  11  (19)| 00:00:01  
 |   5 |    TABLE ACCESS FULL   | SERV            |  13 |   338 |   3   (0)| 00:00:01  
 |   6 |    VIEW          | VW_NSO_1          |   1 |  13 |   8  (25)| 00:00:01  
 |*  7 |     HASH JOIN      |               |   1 |  52 |   8  (25)| 00:00:01  
 |   8 |    VIEW        |               |  13 |   338 |   3   (0)| 00:00:01  
 |   9 |     COUNT        |               |     |     |      |       
 |  10 |      TABLE ACCESS FULL | SERV            |  13 |   169 |   3   (0)| 00:00:01  
 |  11 |    VIEW        |               |  13 |   338 |   4  (25)| 00:00:01  
 |  12 |     WINDOW SORT    |               |  13 |   169 |   4  (25)| 00:00:01  
 |  13 |      COUNT       |               |     |     |      |       
 |  14 |       TABLE ACCESS FULL| SERV            |  13 |   169 |   3   (0)| 00:00:01  
 |  15 |   INTERSECTION       |               |     |     |      |       
 |  16 |  SORT UNIQUE       |               |  13 |   338 |   3  (34)| 00:00:01  
 |  17 |   VIEW           |               |  13 |   338 |   2   (0)| 00:00:01  
 |  18 |    TABLE ACCESS FULL   | SYS_TEMP_0FD9D6603_26EA5E |  13 |   338 |   2   (0)| 00:00:01 
 |  19 |  SORT UNIQUE       |               |  13 |  1014 |   9  (78)| 00:00:01  
 |  20 |   UNION-ALL        |               |     |     |      |       
 |  21 |    VIEW          |               |  13 |   338 |   2   (0)| 00:00:01  
 |  22 |     TABLE ACCESS FULL  | SYS_TEMP_0FD9D6603_26EA5E |  13 |   338 |   2   (0)| 00:00:0 
 |  23 |    VIEW          |               |  13 |   338 |   2   (0)| 00:00:01  
 |  24 |     TABLE ACCESS FULL  | SYS_TEMP_0FD9D6603_26EA5E |  13 |   338 |   2   (0)| 00:00:0  
 
 ---------------------------------------------------------------- 
  
 Predicate Information (identified by operation id): 
 --------------------------------------------------- 
  
  4 - access("ACC_NBR"="$nso_col_1") 
  7 - access("A"."RN"="B"."RN" AND "A"."ACC_NBR"="B"."ACC_NBR") 
  
 Note 
 ----- 
  - dynamic sampling used for this statement 
  
 SQL> select distinct C.A1, C.A2 from  
 2   (  
 3   select A.ACC_NBR A1, A.SERV_ID A2, B.ACC_NBR B1, B.SERV_ID B2 from serv A  
 4     left join serv B on A.ACC_NBR = B.ACC_NBR  
 5   ) C where abs(C.A2 - C.B2)=1 and abs(C.A2 - C.B2) <> 0 order by C.A1; 
  
 执行计划 
 ---------------------- 
 Plan hash value: 1958591425 
  
 ----------------------------------------- 
 | Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time   | 
 ----------------------------------------- 
 |   0 | SELECT STATEMENT   |    |  34 |  1768 |   9  (34)| 00:00:01 | 
 |   1 |  SORT UNIQUE     |    |  34 |  1768 |   8  (25)| 00:00:01 | 
 |*  2 |   FILTER       |    |     |     |      |      | 
 |*  3 |  HASH JOIN OUTER   |    |  34 |  1768 |   7  (15)| 00:00:01 | 
 |   4 |   TABLE ACCESS FULL| SERV |  13 |   338 |   3   (0)| 00:00:01 | 
 |   5 |   TABLE ACCESS FULL| SERV |  13 |   338 |   3   (0)| 00:00:01 | 
 ----------------------------------------- 
  
 Predicate Information (identified by operation id): 
 --------------------------------------------------- 
  
  2 - filter(ABS("A"."SERV_ID"-"B"."SERV_ID")=1 AND 
       ABS("A"."SERV_ID"-"B"."SERV_ID") <>0) 
  3 - access("A"."ACC_NBR"="B"."ACC_NBR"(+)) 
  
 Note 
 ----- 
  - dynamic sampling used for this statement