SQLServer2005中的CTE递归查询得到一棵树

最近研究了一下CTE递归查询，感觉这个CTE递归查询蛮好用的，在网上找到了一个比较好的例子，测试例子如下 1 use City; 2 go 3 create table Tree 4 ( 5 ID int identity ( 1 , 1 ) primary key not null , 6 Name varchar ( 20 ) not null , 7 Parent varcha

　　最近研究了一下CTE递归查询，感觉这个CTE递归查询蛮好用的，在网上找到了一个比较好的例子，测试例子如下

<span> 1</span> <span>use</span><span> City;
</span><span> 2</span> <span>go</span>
<span> 3</span> <span>create</span> <span>table</span><span> Tree
</span><span> 4</span> <span>(
</span><span> 5</span>   ID <span>int</span> <span>identity</span>(<span>1</span>,<span>1</span>) <span>primary</span> <span>key</span> <span>not</span> <span>null</span><span>,
</span><span> 6</span>   Name <span>varchar</span>(<span>20</span>) <span>not</span> <span>null</span><span>,
</span><span> 7</span>   Parent <span>varchar</span>(<span>20</span>) <span>null</span>
<span> 8</span> <span>)
</span><span> 9</span> <span>go</span>
<span>10</span> <span>insert</span> Tree <span>values</span>(<span>'</span><span>大学</span><span>'</span>,<span>null</span><span>)
</span><span>11</span> <span>insert</span> Tree <span>values</span>(<span>'</span><span>学院</span><span>'</span>,<span>'</span><span>大学</span><span>'</span><span>)
</span><span>12</span> <span>insert</span> Tree <span>values</span>(<span>'</span><span>计算机学院</span><span>'</span>,<span>'</span><span>学院</span><span>'</span><span>)
</span><span>13</span> <span>insert</span> Tree <span>values</span>(<span>'</span><span>网络工程</span><span>'</span>,<span>'</span><span>计算机学院</span><span>'</span><span>)
</span><span>14</span> <span>insert</span> Tree <span>values</span>(<span>'</span><span>信息管理</span><span>'</span>,<span>'</span><span>计算机学院</span><span>'</span><span>)
</span><span>15</span> <span>insert</span> Tree <span>values</span>(<span>'</span><span>电信学院</span><span>'</span>,<span>'</span><span>学院</span><span>'</span><span>)
</span><span>16</span> <span>insert</span> Tree <span>values</span>(<span>'</span><span>教务处</span><span>'</span>,<span>'</span><span>大学</span><span>'</span><span>)
</span><span>17</span> <span>insert</span> Tree <span>values</span>(<span>'</span><span>材料科</span><span>'</span>,<span>'</span><span>教务处</span><span>'</span><span>)
</span><span>18</span> <span>insert</span> Tree <span>values</span>(<span>'</span><span>招生办</span><span>'</span>,<span>'</span><span>大学</span><span>'</span><span>)
</span><span>19</span> <span>go</span>
<span>20</span> <span>with</span> CTE <span>as</span>
<span>21</span> <span>(
</span><span>22</span> <span>--</span><span>>Begin 一个定位点成员</span>
<span>23</span>  <span>select</span> ID, Name,Parent,<span>cast</span>(Name <span>as</span> <span>nvarchar</span>(<span>max</span>)) <span>as</span> TE,<span>0</span> <span>as</span> Levle <span>from</span> Tree <span>where</span> Parent <span>is</span> <span>null</span>
<span>24</span> <span>--</span><span>>End </span>
<span>25</span> <span>union</span> <span>all</span>
<span>26</span> <span>--</span><span>>Begin一个递归成员</span>
<span>27</span>  <span>select</span> Tree.ID, Tree.Name,Tree.Parent,<span>cast</span>(<span>replicate</span>(<span>'</span> <span>'</span>,<span>len</span>(CTE.TE))<span>+</span><span>'</span><span>|_</span><span>'</span><span>+</span>Tree.name <span>as</span> <span>nvarchar</span>(<span>MAX</span>)) <span>as</span> TE,Levle<span>+</span><span>1</span> <span>as</span><span> Levle
</span><span>28</span>         <span>from</span> Tree <span>inner</span> <span>join</span><span> CTE
</span><span>29</span>         <span>on</span> Tree.Parent<span>=</span><span>CTE.Name
</span><span>30</span> <span>--</span><span>>End</span>
<span>31</span> <span>)
</span><span>32</span> <span>select</span> <span>*</span> <span>from</span> CTE <span>order</span> <span>by</span><span> ID
</span><span>33</span> <span>--</span><span>1.将 CTE 表达式拆分为定位点成员和递归成员。</span>
<span>34</span> <span>--</span><span>2.运行定位点成员，创建第一个调用或基准结果集 (T0)。</span>
<span>35</span> <span>--</span><span>3.运行递归成员，将 Ti 作为输入(这里只有一条记录)，将 Ti+1 作为输出。</span>
<span>36</span> <span>--</span><span>4.重复步骤 3，直到返回空集。</span>
<span>37</span> <span>--</span><span>5.返回结果集。这是对 T0 到 Tn 执行 UNION ALL 的结果。</span>

上面的SQL语句再次插入一条数据：

insert Tree values('网络1班','网络工程')

     运行结果如下图：

图1 运行结果

注意点：貌似在递归成员处所选择的字段都必须Tree表的数据，而不能是CTE结果集中的除了Tree中没有而CTE中有的字段在这里才可以引用，比如字段TE。

    首先看下，遍历的第1条记录的SQL语句：

<span>1</span> <span>select</span> ID, Name,Parent,<span>cast</span>(Name <span>as</span> <span>nvarchar</span>(<span>max</span>)) <span>as</span> TE,<span>0</span> <span>as</span> Levle <span>from</span> Tree <span>where</span> Parent <span>is</span> <span>null</span>

获取的结果为：

   Name  Parent   TE    Levle

-------------------------------------

     大学    NULL   大学     0

   递归第2次所获取的结果集合的类SQL语句为：

<span>1</span> <span>select</span> Tree.ID, Tree.Name,Tree.Parent,<span>cast</span>(<span>replicate</span>(<span>'</span> <span>'</span>,<span>len</span>(CTE.TE))<span>+</span><span>'</span><span>|_</span><span>'</span><span>+</span>Tree.name <span>as</span> <span>nvarchar</span>(<span>MAX</span>)) <span>as</span> TE,Levle<span>+</span><span>1</span> <span>as</span><span> Levle
</span><span>2</span> <span>from</span> Tree <span>inner</span> <span>join</span>
<span>3</span>    (<span>select</span> ID, Name,Parent,<span>cast</span>(Name <span>as</span> <span>nvarchar</span>(<span>max</span>)) <span>as</span> TE,<span>0</span> <span>as</span> Levle <span>from</span> Tree <span>where</span> Parent <span>is</span> <span>null</span><span>)
</span><span>4</span> <span>as</span><span> CTE
</span><span>5</span> <span>on</span> Tree.Parent<span>=</span>CTE.Name

上面的CTE子查询的结果就是第一次递归查询的结果集，上面SQL运行结果为：

同样的，将第二次递归查询的上面三条记录作为第三次查询的‘定位成员’：

  【这里要注意，上面的三条记录是从最后一条开始依次作为第三次递归的输入的，即第一条是ID=9的记录，接下来是7和2，关于第四次递归也类似】

   第三次递归类SQL语句

<span>1</span> <span>select</span> Tree.ID, Tree.Name,Tree.Parent,<span>cast</span>(<span>replicate</span>(<span>'</span> <span>'</span>,<span>len</span>(CTE.TE))<span>+</span><span>'</span><span>|_</span><span>'</span><span>+</span>Tree.name <span>as</span> <span>nvarchar</span>(<span>MAX</span>)) <span>as</span> TE,Levle<span>+</span><span>1</span> <span>as</span><span> Levle
</span><span>2</span> <span>from</span> Tree <span>inner</span> <span>join</span>
<span>3</span> (第二次递归查询的SQL语句)<span>as</span><span> CTE
</span><span>4</span> <span>on</span> Tree.Parent<span>=</span>CTE.Name

结果如下：

其实每次递归的类SQL可为如下所示：

<span>1</span> <span>select</span> Tree.ID, Tree.Name,Tree.Parent,<span>cast</span>(<span>replicate</span>(<span>'</span> <span>'</span>,<span>len</span>(CTE.TE))<span>+</span><span>'</span><span>|_</span><span>'</span><span>+</span>Tree.name <span>as</span> <span>nvarchar</span>(<span>MAX</span>)) <span>as</span> TE,Levle<span>+</span><span>1</span> <span>as</span><span> Levle
</span><span>2</span> <span>from</span> Tree <span>inner</span> <span>join</span>
<span>3</span> <span>(上次递归查询的结果集，仅仅是上次那一次的，而不是以前的总和结果集)
</span><span>4</span> <span>as</span><span> CTE
</span><span>5</span> <span>on</span> Tree.Parent<span>=</span>CTE.Name

第四次递归一次类推，最后所查询的结果为上面所有递归的union。

 续：在上面的SQ语句查询结果中，ID为10的记录应该要放在ID为4的后面。

 往数据表中再次添加两条记录：

insert Tree values('计科','计算机学院') insert Tree values('我','网络1班') 

再次修改上面的SQL语句：

<span> 1</span> <span>with</span> CTE <span>as</span>  
<span> 2</span> <span>(   
</span><span> 3</span> <span>--</span><span>>Begin 一个定位点成员   </span>
<span> 4</span>  <span>select</span> ID, Name,Parent,<span>cast</span>(Name <span>as</span> <span>nvarchar</span>(<span>max</span>)) <span>as</span><span> TE,
</span><span> 5</span>         ROW_NUMBER()<span>over</span>(<span>order</span> <span>by</span> <span>getdate</span>()) <span>as</span><span> OrderID
</span><span> 6</span>         <span>--</span><span>最关键是上面这个字段，要获取排序字段，按字符串来排序。</span>
<span> 7</span>         <span>--</span><span>其中窗口函数必须要使用order by，但是不能用整型，那就用时间吧</span>
<span> 8</span>         <span>from</span> Tree <span>where</span> Parent <span>is</span> <span>null</span>  
<span> 9</span> <span>--</span><span>>End    </span>
<span>10</span> <span>union</span> <span>all</span>   
<span>11</span> <span>--</span><span>>Begin一个递归成员   </span>
<span>12</span>  <span>select</span> Tree.ID, Tree.Name,Tree.Parent,<span>cast</span>(<span>replicate</span>(<span>'</span> <span>'</span>,<span>len</span>(CTE.TE))<span>+</span><span>'</span><span>|_</span><span>'</span><span>+</span>Tree.name <span>as</span> <span>nvarchar</span>(<span>MAX</span>)) <span>as</span><span> TE,
</span><span>13</span>         CTE.OrderID<span>*</span><span>100</span><span>+</span>ROW_NUMBER()<span>over</span>(<span>Order</span> <span>by</span> <span>GETDATE</span>()) <span>as</span><span> OrderID
</span><span>14</span>         <span>from</span> Tree <span>inner</span> <span>join</span><span> CTE   
</span><span>15</span>         <span>on</span> Tree.Parent<span>=</span><span>CTE.Name   
</span><span>16</span> <span>--</span><span>>End   </span>
<span>17</span> <span>)   
</span><span>18</span> <span>select</span> <span>*</span> <span>from</span><span> CTE
</span><span>19</span> <span>order</span> <span>by</span> <span>LTRIM</span>(OrderID)<span>--</span><span>最后将这个整型数据转换为字符串型的进行排序</span>
<span>20</span> 
<span>21</span> <span>--</span><span>有时候整型可以比大小，字符串也可以，字符串比的大小是一位一位进行字符比较的</span>
<span>22</span> <span>--</span><span>整型+字符串==整型，只有字符串+字符串==两个字符串的并和</span>
<span>23</span> <span>--</span><span>递归查询中：第二条记录可以引用第一条记录的值</span>
<span>24</span> <span>--</span><span>动态加载记录时，同一个等级的记录识别符：RowNumber()over(order by getdate())</span>
<span>25</span> <span>--</span><span>延伸：可以动态获取某个部门下的所以子部门。也可以获取该部门上级的所以部门</span>
<span>26</span> 
<span>27</span> <span>--</span><span>总结：首先要拼凑出一个整型数据，然后转换为字符串，最后是进行字符串的order，而不是整型数据的order，</span>

图2 运行结果

这样，无论用户插入多少条记录都可以进行按部门，按规律进行查询。