假如我们有一个序列，是从400100到499999，但是其中有序列中有缺失，我们需要找在序列中有哪些间隔，我们应该怎么去找呢？
我们来看看这个例子：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
SQL> select * from test_table order by menuid; …… MENUID NAME ---------- ---------------------------------------- 408090 aa 408091 aa 408092 aa 408093 aa 408094 aa 408095 aa 408096 aa 408097 aa 408098 aa 408099 aa<-----注意此处，序 408200 aa<-----列不连续。 408201 aa 408202 aa 408203 aa 408204 aa 408205 aa 408206 aa 408207 aa 408208 aa 408209 aa …… 439379 aa 439380 aa<----注意此处，序 439382 aa<----列不连续。 439383 aa 439384 aa 439385 aa 439386 aa 439387 aa 439388 aa …… |
现有一个表myuser.test_table，其中有一列menuid，这个menuid是用序列insert的，但是由于某些情况，menuid本应该从400100到499999，却发生了一些缺失，如400100到408099，本来下一个应该是408100，现在却变成408200；439380的下一个本应该是439381，现在却变成439382……总之，在menuid中发生很多缺失。我们需要找出其中哪些缺失，以便于重新添加序列：
（1）建临时表400101～499999，这个表中包含完整的从400100到499999的数字：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE xxx AS SELECT ROWNUM aa FROM dba_source WHERE ROWNUM<=499999; CREATE TABLE yyy AS SELECT ROWNUM aa FROM dba_source WHERE ROWNUM<=400100; CREATE TABLE zzz AS SELECT * FROM xxx MINUS SELECT * FROM yyy; DROP TABLE xxx; DROP TABLE yyy; |
（2）将myuser.test_table表的序列取出
|
1 2 |
CREATE TABLE kk AS SELECT menuid FROM myuser.test_table |
（3）做参照临时表，该表中有是将完整的序列的数据和test_table中不完整的数据合并在一起：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
CREATE TABLE sssk AS SELECT zzz.aa,kk.menuid FROM zzz,kk WHERE zzz.aa=kk.menuid(+) --我们可以先看看这个临时参照表是个什么样子： select * from sssk where rownum<200; …… AA MENUID ---------- ---------- 400277 400277 400278 400278 400279 400279 400280 400280 400281 400282 400282 400283 400283 400284 400285 400286 400287 400287 …… |
我们看到，临时参照表中，如果test_table中的序列缺失，就会体现在该表中，有些缺失1个序列，有些缺失3个序列。
（4）做间隔分布表
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE mygod AS SELECT * FROM ( SELECT lag(b.menuid) over(PARTITION BY NULL ORDER BY aa) kka FROM sssk b ) WHERE kka IS NOT NULL UNION SELECT 400100 FROM dual UNION SELECT 499999 FROM dual; |
（5）出结果
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT * FROM (SELECT * FROM (SELECT kka menuid, lag(kka) over(ORDER BY kka) last_num, kka - lag(kka) over(ORDER BY kka) minus_num FROM mygod ORDER BY kka) ORDER BY minus_num DESC NULLS LAST) WHERE rownum < 6; --我们取的是序列缺失前五位： MENUID LAST_NUM MINUS_NUM ---------- ---------- ---------- 482000 480999 1001 408200 408099 101 418200 418099 101 438200 438099 101 428200 428099 101 |
MENUID 表示test_table中的字段MENUID，它的值是一个序列；LAST_NUM是根据大小排列，menuid的上一位；MINUS_NUM表示期间缺失的序列数。我们看到，序列从480999到482000缺失最多，期间少了1001个，这一段的序列缺失最多的部分。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SQL> select * from test_table where menuid>=480990 and menuid<=482009 order by menuid; MENUID NAME ---------- ---------------------------------------- 480990 aa 480991 aa 480992 aa 480993 aa 480994 aa 480995 aa 480996 aa 480997 aa 480998 aa 480999 aa<-------------此区间，序列丢 482000 aa<-------------失是最多的，上一个为480999，下一个为482000。 482002 aa 482003 aa 482004 aa 482005 aa 482006 aa 482007 aa 482008 aa 482009 aa |
（6）最后，我们已经找到了序列的间隔，清理战场：
|
1 2 3 4 |
DROP TABLE zzz; DROP TABLE kk; DROP TABLE sssk; DROP TABLE mygod; |