SQL connect by递归查询

今天有空学习了Oracle的递归查询connect by语句，想借助博客把我的理解与你分享，如果有什么不足请多多包含。

Oracle中的start with ... connect by...语句可以实现递归查询。我们就用该语句实现将树形结构保存到表中。表结构如下：

create table TREETEST
(
    cityid   INTEGER not null, -- 行政区划代码
    cityname VARCHAR2(100),    -- 行政区划名称
    parname  VARCHAR2(100),    -- 上级行政区划代码
    salary   NUMBER(19,6)      -- 当前行政区划的平均薪水（随意填写的，不具有参考价值）
)

 现在我将下面树状图的数据保存到数据库中，如下图：

 添加到数据库后的结构如下图所示：

 从上图中不能明显的看出行政区划的层级关系，现在我们通过connect by语句在每行的行政区划前面加入空格，使呈现出明显的层级关系，SQL语句如下：

select Level , cityid, parname, cityname, salary,
          lpad(' ', 8*(Level-1)) || cityname as parentName 
  from treetest a 
start with a.cityid=1 connect by a.parname = prior a.cityid;

 结果如下：

 其中“parentName”列展示了行政级别的层级关系。解释一下以上SQL语句的具体含义：

start with语句表示树从什么位置开始进行检索。

    如：start with a.cityid=1（从行政区划代码等于1的行政区划开始进行检索，即从“中国”开始进行检索）

connect by语句表示当前数据行和下一行数据之间的关系。

prior 语句表示那个字段属于前一行（英文解释：优先的；在先的，在前的）

    如：connect by a.parname = prior a.cityid;

           表示当前行的parname等于上一行的cityid。

 

上面属于从根部（root）往叶子（children）节点进行检索，现在我们演示从叶子节点往根部进行检索，只需要将connect by后面的条件进行交换即可，如：检索“金牛区”的所有上级行政区划

select Level , cityid, parname, cityname, salary, 
          lpad(' ', 8*(Level-1)) || cityname as parentName 
  from treetest a 
start with a.cityid=14 connect by a.cityid = prior a.parname;

 其中

start with a.cityid=14 表示从“金牛区”开始检索

connect by a.cityid = prior a.parname 表示当前行的cityid 等于上一行的parname 

结果如下：

 

从“北京”检索所有属于北京的行政区划，SQL代码：

select Level , cityid, parname, cityname, salary, 
          lpad(' ', 8*(Level-1)) || cityname as parentName 
  from treetest a
start with a.cityid=2 connect by a.parname = prior a.cityid;

 结果如下：

 

使用connect by过滤掉某些行政区划，那么这个行政区划的所有子行政区划也将被过滤掉。如：不检索“上海市”及其子行政区划，SQL代码：

select Level , cityid, parname, cityname, salary, 
          lpad(' ', 8*(Level-1)) || cityname as parentName 
  from treetest a
start with a.cityid=1 connect by a.parname = prior a.cityid and a.cityid!=3;

 结果如下：

 

我们也可以使用where子句进行过滤操作，但是不会将指定行政区划的所有子行政区划过滤掉。我们还是以过滤“上海市”为列，SQL代码：

select Level , cityid, parname, cityname, salary, 
          lpad(' ', 8*(Level-1)) || cityname as parentName 
  from treetest a where a.cityid!=3
start with a.cityid=1 connect by a.parname = prior a.cityid;

结果如下：

 

 基本规则：

a、使用connect by时各个子句的顺序为：

     1. select

     2. from

     3. where

     4. start with

     5. connect by

     6. order by

 b、prior强制树的顺序变为从根到叶或从叶到根

 c、where子句可以从树中排除个体，但是不能排除它们的子孙

 d、connect by中的条件可以排除个体也能排除所有它们的子孙

 e、connect by不能和where子句中表连接使用

 

 

 

 

 

 

 

评论

1 楼 masuweng 2017-10-20  

      楼主辛苦了 很好的例子!