求助高手帮忙写个SQL------急急急！！！

Question

用一个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

Answer 1

SQL> select * from serv;   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          512 rows selectedSQL> 
SQL> 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  ;   ACC_NBR    SERV_ID
---------- ----------
         1          1
         1          2
         1          3
         1          4
         1          6
         2          2
         2          3
         5          1
         5          4
         5          510 rows selected

Answer 2

看这个行不行
SELECT acc_nbr, serv_id
  FROM (SELECT t2.*, COUNT (1) OVER (PARTITION BY acc_nbr, rn) cnt
          FROM (SELECT t1.*, t1.serv_id - ROWNUM rn
                  FROM serv t1) t2)
 WHERE cnt >= 2

Answer 3

没看仔细，再加工下，加1减1取交集见如下：
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  ;   ACC_NBR    SERV_ID
---------- ----------
         1          1
         1          2
         1          3
         1          4
         2          2
         2          3
         5          4
         5          58 rows selected

Answer 4

也不行比如:SQL> insert into serv values(2,4);已创建 1 行。SQL> commit;提交完成。SQL> select * from serv;   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   ACC_NBR    SERV_ID
---------- ----------
         5          5
         2          4已选择13行。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 serv t1) t2)
  5   WHERE cnt >= 2;   ACC_NBR    SERV_ID
---------- ----------
         1          2
         1          3
         1          4
         1          1
         2          2
         2          3
         5          4
         5          5已选择8行。
你这个差了一行

Answer 5

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     ;   ACC_NBR    SERV_ID
---------- ----------
         1          1
         1          2
         1          3
         1          4
         2          2
         2          3
         2          4
         5          4
         5          5已选择9行。你搞定了!!!

Answer 6

如果你的表里数据顺序不确定的话...先排个序就行了
SELECT acc_nbr, serv_id
  FROM (SELECT t2.*, COUNT (1) OVER (PARTITION BY acc_nbr, rn) cnt
          FROM (SELECT t1.*, t1.serv_id - ROWNUM rn
                  FROM (select * from serv order by acc_nbr, serv_id)t1) t2)
 WHERE cnt >= 2

Answer 7

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 statementSQL> 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 statementSQL> 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
我本机比较的执行计划
8楼的计划看起来比较优

Answer 8

我刚反复向表中插入记录, 试图对大数据量下sql性能作比较的时候,
发现8楼的sql出错了
SQL> select count(*) from serv;
 
  COUNT(*)
----------
    212992SQL> 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;
 
    ACC_NBR     SERV_ID
----------- -----------
          1           6
          1           4
          1           4
          1           6
          1           4
          1           3
          1           3
          1           2
          1           2
          1           1
          2           4
          2           3
          2           3
          2           2
          5           5
          5           4
          5           4
          5           1
          5           4
          5           1
 
    ACC_NBR     SERV_ID
----------- -----------
          5           4
          5           1
 
22 rows selectedSQL> 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
  6  ;
 
         A1          A2
----------- -----------
          1           1
          1           2
          1           3
          1           4
          2           2
          2           3
          2           4
          5           4
          5           5
 
9 rows selectedSQL> 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
 12      (select acc_nbr,serv_id+1 from t union  select acc_nbr,serv_id-1 from t);
 
    ACC_NBR    SERV_ID
----------- ----------
          1          1
          1          2
          1          3
          1          4
          2          2
          2          3
          2          4
          5          4
          5          5
 
9 rows selected