Oracle层次查询和分析函数在号段选取中的应用

lodezao · 发表于 2013-4-1 22:44

思想是不会过时滴！！！

lastwinner · 发表于 2013-4-2 02:50

lodezao 发表于 2013-4-1 22:44
思想是不会过时滴！！！

开发语言很有可能会过时，但算法很少有可能会过时

hh7yx · 发表于 2013-7-22 17:34

今天发了个帖子，刚好是关于号段的，搜索到了这个精华帖，很不错
不过看的过程中，发现了一个错误，如下。。。。。。

3.3.1       题例
table T，列：serial_no
我想能够查询一下serial_no这个字段的不连续的值。
例如：
serial_no
1
2
3
4
6
8
9
10
我想一个sql语句查出来缺失的号码，
显示结果为：
5
7
--------------------------
--------------------------
create table test_3(serial_no number);
insert into test_3 values(1);
insert into test_3 values(2);
insert into test_3 values(3);
insert into test_3 values(4);
insert into test_3 values(6);
insert into test_3 values(8);
insert into test_3 values(9);
insert into test_3 values(10);
commit;

--版主提供的sql好像结果不对
select distinct s + level - 1 rlt
  from (select lag(serial_no, 1) over(order by serial_no) + 1 S,
            serial_no - 1 E
      from test_3 t)
where E - S <> 0
connect by level <= e - s
order by 1;

--正确的应该是这样的
select distinct s + level - 1 rlt
  from (select lag(serial_no, 1) over(order by serial_no) + 1 S,
            serial_no - 1 E
      from test_3 t)
where E - S = 0  or e - s > 1
connect by level <= e - s+1
order by 1;

--不过如果再加一行大一点的数字，上面的sql效率不怎么样
insert into test_3 values(1700);

--这样写，效率还可以（udfrog提供的）
select s + level - 1 serial_no
  from (select lag(serial_no, 1) over(order by serial_no) + 1 S,
            serial_no - 1 E
      from test_3)
where E - S = 0
or e - s > 1
connect by prior s = s
   and level <= e - s + 1
   and prior dbms_random.value is not null
order by 1;

lastwinner · 发表于 2013-7-23 02:32

hh7yx 发表于 2013-7-22 17:34
今天发了个帖子，刚好是关于号段的，搜索到了这个精华帖，很不错
不过看的过程中，发现了一个错误，如下。 ...

这问题在书里都改了，论坛懒得改了

dyfriend0123 · 发表于 2013-9-11 11:43

hh7yx 发表于 2013-7-22 17:34
今天发了个帖子，刚好是关于号段的，搜索到了这个精华帖，很不错
不过看的过程中，发现了一个错误，如下。 ...

select s + level - 1 serial_no
  from (select lag(serial_no, 1) over(order by serial_no) + 1 S,
            serial_no - 1 E
      from test_3)
where E - S = 0
or e - s > 1
connect by prior s = s
   and level <= e - s + 1
   and prior dbms_random.value is not null
order by 1;

俺是初学者，能说下这个思路不，connect by  后面都看不懂了