用一个sql实现以下功能：
serv表，有acc_nbr和serv_id两个字段，其中有些相同的acc_nbr的serv_id是连续的，需要把这些acc_nbr和连续的serv_id找出来。
如
acc_nbr serv_id
1 1
1 2
1 3
1 4
1 6
2 2
2 3
3 1
4 2
5 1
5 4
5 5
要求取出的结果是
acc_nbr serv_id
1 1
1 2
1 3
1 4
2 2
2 3
5 4
5 5
------解决方案--------------------
- 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