如何找出有不连续seq的记录

chengccy2010

KEN6503 发表于 2017-3-18 06:42
用4楼的思路，稍改动一下就可以了。
WITH T AS (
SELECT 100 AS ID, 1 AS SEQ FROM DUAL

其实就用你自己的SQL套一层就行;
WITH T AS (
SELECT 100 AS ID, 1 AS SEQ FROM DUAL
UNION ALL
SELECT 100 AS ID, 2 AS SEQ FROM DUAL
UNION ALL
SELECT 100 AS ID, 3 AS SEQ FROM DUAL
UNION ALL
SELECT 200 AS ID, 1 AS SEQ FROM DUAL
UNION ALL
SELECT 200 AS ID, 2 AS SEQ FROM DUAL
UNION ALL
SELECT 200 AS ID, 4 AS SEQ FROM DUAL
UNION ALL
SELECT 300 AS ID, 1 AS SEQ FROM DUAL
UNION ALL
SELECT 300 AS ID, 2 AS SEQ FROM DUAL
UNION ALL
SELECT 300 AS ID, 2 AS SEQ FROM DUAL
UNION ALL
SELECT 300 AS ID, 4 AS SEQ FROM DUAL)
SELECT *
FROM   (SELECT T1.*,
                MAX(CASE
                       WHEN SEQ = LAG_1 OR
                            LAG_1 + 1 != SEQ THEN
                        1
                       ELSE
                        0
                     END) OVER(PARTITION BY ID) RN
         FROM   (SELECT ID, SEQ,
                         LAG(SEQ) OVER(PARTITION BY ID ORDER BY SEQ) LAG_1
                  FROM   T) T1)
WHERE  RN = 1

juan025

lastwinner 发表于 2017-3-20 11:39
谢谢推荐，原文在此 http://www.itpub.net/thread-719692-1-1.html

那个时候都是那么的青涩呀

bell6248

方法很多

SQL> WITH T AS (
  2  SELECT 100 AS ID, 1 AS SEQ FROM DUAL
  3  UNION ALL
  4  SELECT 100 AS ID, 2 AS SEQ FROM DUAL
  5  UNION ALL
  6  SELECT 100 AS ID, 3 AS SEQ FROM DUAL
  7  UNION ALL
  8  SELECT 200 AS ID, 1 AS SEQ FROM DUAL
  9  UNION ALL
10  SELECT 200 AS ID, 2 AS SEQ FROM DUAL
11  UNION ALL
12  SELECT 200 AS ID, 4 AS SEQ FROM DUAL
13  UNION ALL
14  SELECT 300 AS ID, 1 AS SEQ FROM DUAL
15  UNION ALL
16  SELECT 300 AS ID, 2 AS SEQ FROM DUAL
17  UNION ALL
18  SELECT 300 AS ID, 1 AS SEQ FROM DUAL)
19  select  id,
20          seq
21  from
22  (select id,
23          seq,
24          sum(diff) over(partition by id) diff
25  from
26  (select id,
27          seq,
28          seq - row_number() over(partition by id order by seq) diff
29     from t))
30  where diff <> 0;

        ID        SEQ
---------- ----------
       200          1
       200          2
       200          4
       300          1
       300          1
       300          2

6 rows selected

SQL>

lastwinner

juan025 发表于 2017-3-20 15:57
那个时候都是那么的青涩呀

咋看出来的青涩？

话说一晃十年过去了

KEN6503

fage090 发表于 2017-3-20 10:33
这个逻辑有点问题吧？ 假如有4行，seq分别是1、2、2、4 。这样不连续又有重复的，就筛选不出来了

确实是。
Itpub搞手真多。谢谢

chengccy2010

bell6248 发表于 2017-3-20 18:02
方法很多

SQL> WITH T AS (

这个不保险, 可能正负相抵;
可以改成 max(nullif(diff,0)) 来取出含非零的ID;
WITH T AS (
  SELECT 300 AS ID, 1 AS SEQ FROM DUAL
  UNION ALL
SELECT 300 AS ID, 2 AS SEQ FROM DUAL
UNION ALL
  SELECT 300 AS ID, 2 AS SEQ FROM DUAL
  UNION ALL
SELECT 300 AS ID, 5 AS SEQ FROM DUAL
UNION ALL
  SELECT 300 AS ID, 5 AS SEQ FROM DUAL)
  select  id,
          seq
  from
  (select id,
         seq,
          sum(diff) over(partition by id) diff
from
  (select id,
          seq,
          seq - row_number() over(partition by id order by seq) diff
     from t))
where diff <> 0;

  select  id,
          seq
  from
  (select id,
         seq,
          max(nullif(diff,0)) over(partition by id) diff
from
  (select id,
          seq,
          seq - row_number() over(partition by id order by seq) diff
     from t))
where diff <> 0;

bell6248

chengccy2010 发表于 2017-3-21 09:35
这个不保险, 可能正负相抵;
可以改成 max(nullif(diff,0)) 来取出含非零的ID;
WITH T AS (

哪里不保险， 你能用数据举例吗？

chengccy2010

bell6248 发表于 2017-3-21 09:46
哪里不保险， 你能用数据举例吗？

我楼上回复的SQL就是例子

bell6248

chengccy2010 发表于 2017-3-21 09:49
我楼上回复的SQL就是例子

其实小改改即可， 代码如下

SQL> WITH T AS (
  2  SELECT 100 AS ID, 1 AS SEQ FROM DUAL
  3  UNION ALL
  4  SELECT 100 AS ID, 2 AS SEQ FROM DUAL
  5  UNION ALL
  6  SELECT 100 AS ID, 3 AS SEQ FROM DUAL
  7  UNION ALL
  8  SELECT 200 AS ID, 1 AS SEQ FROM DUAL
  9  UNION ALL
10  SELECT 200 AS ID, 2 AS SEQ FROM DUAL
11  UNION ALL
12  SELECT 200 AS ID, 4 AS SEQ FROM DUAL
13  UNION ALL
14  SELECT 300 AS ID, 1 AS SEQ FROM DUAL
15  UNION ALL
16  SELECT 300 AS ID, 2 AS SEQ FROM DUAL
17  UNION ALL
18  SELECT 300 AS ID, 1 AS SEQ FROM DUAL
19  union all
20  SELECT 800 AS ID, 1 AS SEQ FROM DUAL
21    UNION ALL
22  SELECT 800 AS ID, 2 AS SEQ FROM DUAL
23  UNION ALL
24    SELECT 800 AS ID, 2 AS SEQ FROM DUAL
25    UNION ALL
26  SELECT 800 AS ID, 5 AS SEQ FROM DUAL
27  UNION ALL
28    SELECT 800 AS ID, 5 AS SEQ FROM DUAL)
29  select  id,
30          seq
31  from
32  (select id,
33          seq,
34          sum(diff) over(partition by id) diff
35  from
36  (select id,
37          seq,
38          abs(seq - row_number() over(partition by id order by seq)) diff
39     from t))
40  where diff <> 0;

        ID        SEQ
---------- ----------
       200          1
       200          2
       200          4
       300          1
       300          2
       300          1
       800          1
       800          2
       800          5
       800          2
       800          5

11 rows selected

SQL>