connect by

Posted on February 24, 2009

对于层级结构的数据，connect by是个非常好用的东西。今天在查询组织层次结构的时候（根据一个部门列出所有下属部门）发现里面还有个小陷阱。原始的语句如下：

SELECT pos.organization_id_child,
       pos.d_child_name,
       pos.organization_id_parent,
       pos.d_parent_name,
       LEVEL,
       sys_connect_by_path(pos.d_child_name, '/') path
  FROM per_org_structure_elements_v pos
WHERE pos.org_structure_version_id = 65 -- 注意这里
 START WITH pos.organization_id_child = 133
CONNECT BY PRIOR pos.organization_id_child = pos.organization_id_parent;

由于组织层次结构有版本控制，所以这里使用WHERE条件筛选当前的层次结构，但是结果中却出来很多同样level的重复数据。尝试换用如下SQL：

SELECT pos.organization_id_child,
       pos.d_child_name,
       pos.organization_id_parent,
       pos.d_parent_name,
       LEVEL,
       sys_connect_by_path(pos.d_child_name, '/') path
  FROM (SELECT *
          FROM per_org_structure_elements_v p
         WHERE p.org_structure_version_id = 65) pos
 START WITH pos.organization_id_child = 133
CONNECT BY PRIOR pos.organization_id_child = pos.organization_id_parent;

出来预期结果。

翻阅了《Oracle® Database SQL Reference》，没有发现语法问题。数据库版本是 Oracle9i Enterprise Edition Release 9.2.0.6.0 。