求缺失的号（读书笔记三）

来源：互联网发布：彩票开奖数据采集网站编辑：程序博客网时间：2024/04/30 21:17

求缺失的号
建表语句：

CREATE TABLE t21(ID NUMBER(3));INSERT INTO t21 VALUES(1);INSERT INTO t21 VALUES(2);INSERT INTO t21 VALUES(3);INSERT INTO t21 VALUES(4);INSERT INTO t21 VALUES(6);INSERT INTO t21 VALUES(8);INSERT INTO t21 VALUES(13);COMMIT;

题目要求：查出缺失的号码
思路：这样的要求，可以使用lag函数找到每个数的前一个数，然后进行比较，如果相差1表示连续的，否则是非连续的，
一旦发现非连续的，则可以通过构造序列来查出中间缺失的数字
第一步：使用lag找到非连续的数，构成一个表

SQL> SELECT a.id,a.qian  2            FROM (SELECT ID,  3                         LAG(ID) OVER(ORDER BY ID) QIAN,  4                         ID - LAG(ID) OVER(ORDER BY ID) CHAZHI  5                    FROM T21) A  6           WHERE A.CHAZHI <> 1;   ID       QIAN---- ----------   6          4   8          6  13          8

第二步：找到间隔最大的数，用level构成序列，构成第二张表

SQL> SELECT ROWNUM XULIE  2            FROM (SELECT MAX(A.CHAZHI) ZHI  3                    FROM (SELECT ID,  4                                 LAG(ID) OVER(ORDER BY ID) QIAN,  5                                 ID - LAG(ID) OVER(ORDER BY ID) CHAZHI  6                            FROM T21) A  7                   WHERE A.CHAZHI <> 1) B  8          CONNECT BY LEVEL < B.ZHI;      XULIE----------         1         2         3         4

第三步：两张表组合

SQL> SELECT D.ID, D.QIAN, C.XULIE, D.QIAN + C.XULIE  2    FROM (SELECT A.ID, A.QIAN  3            FROM (SELECT ID,  4                         LAG(ID) OVER(ORDER BY ID) QIAN,  5                         ID - LAG(ID) OVER(ORDER BY ID) CHAZHI  6                    FROM T21) A  7           WHERE A.CHAZHI <> 1) D,  8         (SELECT ROWNUM XULIE  9            FROM (SELECT MAX(A.CHAZHI) ZHI 10                    FROM (SELECT ID, 11                                 LAG(ID) OVER(ORDER BY ID) QIAN, 12                                 ID - LAG(ID) OVER(ORDER BY ID) CHAZHI 13                            FROM T21) A 14                   WHERE A.CHAZHI <> 1) B 15          CONNECT BY LEVEL < B.ZHI) C;   ID       QIAN      XULIE D.QIAN+C.XULIE---- ---------- ---------- --------------   6          4          1              5   8          6          1              7  13          8          1              9   6          4          2              6   8          6          2              8  13          8          2             10   6          4          3              7   8          6          3              9  13          8          3             11   6          4          4              8   8          6          4             10  13          8          4             12

第四步：加上where条件，来控制比较过滤一下，得到最后的结果

SQL> SELECT D.ID, D.QIAN, C.XULIE, D.QIAN + C.XULIE  2    FROM (SELECT A.ID, A.QIAN  3            FROM (SELECT ID,  4                         LAG(ID) OVER(ORDER BY ID) QIAN,  5                         ID - LAG(ID) OVER(ORDER BY ID) CHAZHI  6                    FROM T21) A  7           WHERE A.CHAZHI <> 1) D,  8         (SELECT ROWNUM XULIE  9            FROM (SELECT MAX(A.CHAZHI) ZHI 10                    FROM (SELECT ID, 11                                 LAG(ID) OVER(ORDER BY ID) QIAN, 12                                 ID - LAG(ID) OVER(ORDER BY ID) CHAZHI 13                            FROM T21) A 14                   WHERE A.CHAZHI <> 1) B 15          CONNECT BY LEVEL < B.ZHI) C 16   WHERE D.ID > D.QIAN + C.XULIE 17     AND D.QIAN < D.QIAN + C.XULIE;   ID       QIAN      XULIE D.QIAN+C.XULIE---- ---------- ---------- --------------   6          4          1              5   8          6          1              7  13          8          1              9  13          8          2             10  13          8          3             11  13          8          4             12

------------华丽的分割线---------------
上面的思路逻辑是根据‘根据号段求出包含的数（读书笔记二）’中的思路，不过这样写显然显得超级麻烦，但是效率比较高
换另一种思路,即结合start with和connect BY
第一步：使用lag函数构造表

SQL> SELECT LAG(ID,1) OVER(ORDER BY ID)+1 s,ID e  2  FROM t21;          S    E---------- ----              1         2    2         3    3         4    4         5    6         7    8         9   13

第二步：使用start with和connect BY 构造中间的数

SQL> SELECT DISTINCT S + LEVEL - 1  2    FROM (SELECT LAG(ID, 1) OVER(ORDER BY ID) + 1 S, ID E FROM T21)  3   START WITH E - S > 0  4  CONNECT BY LEVEL < E - S + 1  5   ORDER BY 1;  S+LEVEL-1----------         5         7         9        10        11        12

虽然使用start with和level可以简化写法，但是会有重复的行出现，所以需要用distinct去重，效率不高

0 0