Oracle层次查询和分析应用在号段选取

文章作者 100test 发表时间 2007:03:14 14:02:15
来源 100Test.Com百考试题网

　　1. 问题的提出

　　在实际工作中，我们常常会碰到号段选取的问题，例如：一组连续的数，去掉中间一些数，要求出剩下的数的区间（即号段）

　　例如：一串数字为1，2，3，4，7，9，10，则号段为1-4，7-7，9-10

　　知道号段的起止，要求出该号段内所有的数

　　例如：号段为1-3，15-15，则号段内所有的数为1，2，3，15

　　一组数，中间可能有断点，要求出缺失的数

　　例如：一串数字为1，2，3，4，7，9，10，则缺失的数为5，6，8

　　已知大号段范围及已用号段范围，求可用号段范围

　　例如：大号段范围0-999，已用号段范围0-200，399-599，则可用号段范围为201-398，600-999

　　2. 基础知识

　　先做下热身运动，回顾一下层次查询和lead／lag函数的运用。

　　2.1 伪列rownum和level

　　伪列就是并非在表中真正存在的列。已有很多资料介绍rownum和level这两个伪列。这里只想强调一点，伪列是只针对结果集的。

　　2.2 利用层次查询构造连续的数

　　产生5～8这4个连续的数

　　0select * from （0select rownum 4 from dual connect by rownum<5）；

　　0select * from （0select level 4 from dual connect by level<5）；

　　以8月为界，例如2005年8月1日，之前的在校学生入学年份为2001～2004，之后的为2002～2005.求当前日期下的在校学生入学年份：

　　0select * from （0select to_char（add_months（sysdate， 4）， yyyy） - rownum from dual connect by rownum<5）；

　　2.3 用分析函数Lead和Lag获得相邻行的字段值

0select rn, lag(rn)over(order by rn) previos, lead(rn)over(order by rn) next from (0select rownum 4 rn from dual connect by rownum<5).

RN PREVIOS NEXT
---------- ---------- ----------
5 6
6 5 7
7 6 8
8 7

　　简单的说，在这里，Lag是获得前一行的内容，而Lead是获得后一行的内容。

0select rn, lag(rn,2,-1)over(order by rn) previos, lead(rn,2,-1) over(order by rn) next from (0select rownum 4 rn from dual connect by rownum<5).

RN PREVIOS NEXT
---------- ---------- ----------
5 -1 7
6 -1 8
7 5 -1
8 6 -1

　　这里，通过指定offset参数来获得两行前的内容和两行后的内容，如果offset超出范围并且未设定默认值-1，那么系统会自动将其值设为NULL.