求助高手帮忙写个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> 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
