“孔洞”的个数及“边缘”就是缺失数据的数据段
列如,已知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/