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

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

列如，已知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博客 ” ，链接：http://blog.itpub.net/29893219/viewspace-1851761/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29893219/viewspace-1851761/