ORACLE中的层次结构查询..docVIP

select * from ( ??? select test.*, rownum? my_rownum from ( ?????? select id from test where 1=1 start with id=1?????? connect by prior id = pid? order by id ??? ) test where rownum 3 ) where my_rownum = 1; ? 从Root往树末梢递归 select * from TBL_TEST?start with id=1?connect by prior id = pid ? 从末梢往树ROOT递归 select * from TBL_TEST?start with id=5?connect by prior pid = id ? ORACLE 层次结构在现实生活中是很常见的，因此早期的数据库是层次数据库，如实的反映了这种关系。后来层次数据库被证实是非常笨重的，因此关系数据库取得了成功。但是层次结构依然存在，而在关系数据库中层次并不能得到很好的体现。ORACLE提供了强大的层次结构查询来解决这个问题。 一、?? 层次结构关系大致可分为两种。 一种是层级固定，例如美国分为各个州，州下分县，县下又分镇区和城市。这种情况如果在一张表中用递归来实现并不是很好的选择，将其保存在主从结构的三张表中更容易查询和操作。 另一种是层级可变。如图所示的组件清单： ??可构造层次表如下：?? 二、?? ORACLE ORACLE提供了SELECT查询语句的START WITH和CONNECT BY子句，来实现层次查询。要进行层次查询，必须明了两件事情：识别根记录的条件和子记录中指向其父亲的列名。 看我们的例子，parent_assembly列决定该记录的父亲。由于根记录Airplane没有父亲，因此列parent_assembly为空，而其他记录的该列不为空，凭此可识别根记录。两个条件均已满足，构造查询语句如下： SELECT assembly_id, assembly_name, parent_assembly FROM bill_of_materials START WITH parent_assembly IS NULL CONNECT BY parent_assembly = PRIOR assembly_id; 三、?? CONNECT BY子句 从一定角度看，CONNECT BY可以看作是一种连接的规范。实现递归连接，也可以使用标准sql语句，将BILL_OF_MATERIAL与自身连接起来并返回每行的父记录。 SELECT bom1.assembly_id, bom1.assembly_name, bom2.assembly_id parent FROM bill_of_materials bom1 LEFT OUTER JOIN bill_of_materials bom2 ?? ON bom1.parent_assembly = bom2.assembly_id; ?? 这个sql语句返回的记录和上面那个语句是同样的。唯一的区别是行的顺序不一样。当然行的顺序是非常重要的，自连接的sql语句返回记录的顺序不能反映父子关系。 CONNECT BY可以解决所有父子结构方面的问题。 四、?? WHERE子句 要过滤记录，有两种途径。首先，从查询的根节点方面着手，使用START WITH子句来限制查询特定节点。例如我们可以使用如下语句，只查询飞机上两个座位及其部件的信息： SELECT assembly_id, assembly_name, PRIOR assembly_id parent FROM bill_of_materials START WITH assembly_id IN (205,206) CONNECT BY parent_assembly = PRIOR assembly_id; 但是有时需要其他条件，比如part_number is not null，这时就需要引入WHERE子句。 SELECT assembly_id, assembly_name, PRIOR assembly_id parent FROM bill_of_materials WHERE PART_NUMBER IS NOT NULL START WITH assembly_id IN (205,206) CONNECT BY parent_assembly = PRIOR assembly_id; 上面语句中的过滤条件是在CONNECT BY语句处理完成后才发生作用。当树已经生成，过滤条件才开始影响查