“孔洞”的个数及“边缘”就是缺失数据的数据段

列如，已知emp表的empno列的值不是按步长为1增加的，那么就会有孔洞。

select '孔洞' || row_number() over(order by rn) "孔洞",'[' || min(new_tcode) || '....' || max(new_tcode) || ']' "边缘"

from

(select t1.new_tcode,

t2.empno,

last_value(t2.empno ignore nulls) over(order by t1.new_tcode) rn

from

(SELECT level new_tcode

FROM dual

CONNECT BY level <= (select max(empno) from emp)) t1,

emp t2

where t1.new_tcode= t2.empno(+))

where new_tcode <> rn

group by rn;

ITPub博客

获取表的某列的“孔洞”的个数及“边缘”