树结构查询——一个SQL解决问题

   在公司论坛上看到一个同事得求助帖，问题如下：

---

有两个表结构和数据分别如下：

org（机构基本信息表 ）

org_id(机构id)	org_name(机构名称)
1	立案处
2	1科
3	2科
4	调查处
5	3科
6	1科
7	2科
8	3科
9	处理处
10	1科
11	2科
11	3科

rel （机构关系表）

org_id(机构id)	parent_id (父机构id)
6	1
2	4
3	1
12	1
7	4
10	9
8	4
11	9
5	9

要求用只用一个sql语句查出的结果集类似如下结果：

org_id	org_name
1	立案处
6	立案处1科
3	立案处2科
12	立案处3科
4	调查处
2	调查处1科
7	调查处2科
8	调查处3科
9	处理处
10	处理处1科
11	处理处2科
5	处理处3科

---

select  org.org_id,  replace (sys_connect_by_path(org_name,  ' | ' ), ' | ' )     
   from  org, rel     
  where  org.org_id  =  rel.org_id( + )     
 start  with  rel.parent_id  is   null      
connect  by  prior org.org_id  =  rel.parent_id     
order   by  nvl(rel.parent_id, org.org_id), nvl(rel.parent_id,  ' 0 ' ) || org.org_name    

上面代码给出了正确答案，完全符合题目得要求，通过树查询实现了对数据得分类以及排序，下面就对代码分析一下！

• 首先使用外关联（line3）将两张表关联起来，这样可以在一张表里清晰得看出机构id，机构name以及其父机构信息，我们看一下line1使用了replace和sys_connect_by_path两个函数，sys_connect_by_path函数实现将从父节点到当前行内容以 “path”或者层次元素列表的形式显示出来，这里就是用“|”将父机构名称和自己够名称连接起来，replace就是去掉中间得“|”；
• 其次通过树查询（line4、5）得start with...connect by prior语句根据parent_id进行分类；
• 最后对得到得结果进行排序，这个好理解些，首先是根据parent_id排序，如果parent_id为null则根据org_id 排序，否则根据0与org_name 合成的字符串进行比较。