SQL学习(二) 分级检索查询

SQL学习(二) 分级检索查询

前言

       分级检索查询（Hierarchical Retrieval）也就是我们写报表时采用的树形结构查询。虽然大家平时也在使用，但我发现其实大部分同事都是依葫画瓢，真正理解得不多。接下来我们我们就从语法系统学习下分级检索查询相关知识。

基本语法

        分级检索查询的基本语法如下：

    SELECT [LEVEL], column, expr... 

    FROM   table

   [WHERE condition(s)]

   [START WITH condition(s)]

   [CONNECT BY PRIOR condition(s)] ;

       其中语法中重要的关键字就是 START WITH 和 CONNECT BY PRIOR。

  START WITH 定义分级查询的起点

  CONNECT BY PRIOR 定义了分级查询的条件，是从上往下查询还是从下往上查询。

语句书写

例表   

       为了方便的说明，我以部门表为例，表结构和数据源如下：

       其树形结构如图所示：

起始点和查询顺序

  1.首先是起始点，这个是通过start with 定义，如：

  start with 上级id  is null

  或者

  start with id=0

       2.其次是确定显示顺序，是从上到下，还是从下到上，这个是通过CONNECT BY PRIOR 定义。

       如果我们要从上到下，那条件应该是

CONNECT BY PRIOR  id=上级id

       这里我们对其进行下讲解，CONNECT BY PRIOR 是如何判断顺序的，其语法一般为：

CONNECT BY PRIOR column1 = column2

       其中column1代表起始点数据源对应的字段，如上面的id，代表起始点数据源id, column2代表连接数据源的字段，例子中代表要连接字段的上级id，说得比较复杂，我们还是用举例来直观的说明吧。

       举个sql实际的例子:

   select id,上级id,编码,名称
   from 部门表
   Start With Id=4  

   Connect By Prior    Id    =       上级Id

         （起始数据源对应:id=4）       (连接的字段)

       可以看到，起始点是辅助科室（id=4，上级id=0），顺序是从上到下，显示其以下的所有科室（车库，财务科，结账室等）

   select id,上级id,编码,名称

   from 部门表

   Start With Id=4
   Connect By Prior    上级Id    =       Id

         （起始数据源上级id=0）     （对应连接字段id）

 

级数(LEVEL)  

       通过上面的例子，我们可以看到，虽然已经查询出想要的结果，但是如果不仔细看，可能不容易看出起始点，如果我们想一目了然的看到结果，那我们就需要level这个伪列，现在我们加上这个伪列在看下刚才执行结果。

   select level,id,上级id,编码,名称
   from 部门表
   Start With Id=4
   Connect By Prior    Id    =       上级Id

 

   select level,id,上级id,编码,名称
   from 部门表
   Start With Id=4
   Connect By Prior    上级Id    =       Id

 

限制条件

       我们在上面的例1中看到，level等级有3个，用图表示如下：

 

       现在如果想只统计到level 2，我们举2种情况的例子，如下图：

       通过分析，我们可以看到，情况1中，我们不需要提取财务室后的科室，也就是在财务室这里，查询就断掉，只需要加一个条件:名称<>’财务科’，我们来执行下看。

   select level,id,上级id,编码,名称
   from部门表
   Start With Id=4
   Connect By Prior    Id    =       上级Id
   And  名称<>'财务科'

 

   如果要实现情况 2，那我们只需要加个条件：level<=2就可以实现。

   select level,id,上级id,编码,名称
   from部门表
   Start With Id=4
   Connect By Prior    Id    =       上级Id
   And  Level<=2

      上面的语句，我们也可以用where条件进行限制，语句如下，效果是一样的。

   select level,id,上级id,编码,名称
   from部门表

   where  Level<=2

   Start With Id=4
   Connect By Prior    Id    =       上级Id

结语

       通过本章的学习，我想大家至少应该掌握以下几点：了解分级检索查询的概念；知道了如何定义起点和查询顺序；最后如何通过限制条件查出想要的结果。通过介绍，希望能对以后大家工作中书写报表有所帮助。