SQLServer2005中的CTE递归查询得到一棵树-mysql教程-PHP中文网

首页

数据库

mysql教程

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 03:40 PM

查询递归

最近研究了一下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班','网络工程')

运行结果如下图：

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

图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运行结果为：

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

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

【这里要注意，上面的三条记录是从最后一条开始依次作为第三次递归的输入的，即第一条是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

登录后复制

结果如下：

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

其实每次递归的类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>

登录后复制

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

图2 运行结果

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

本站声明

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn

热AI工具

热工具

热门话题

gmail邮箱登陆入口在哪里

7333

Java教程

1627

CakePHP 教程

1351

Laravel 教程

1263

PHP教程

1209

显示更多

Related knowledge

C++ 函数的递归实现：递归深度有限制吗？ Apr 23, 2024 am 09:30 AM

C++函数的递归深度受到限制，超过该限制会导致栈溢出错误。限制值因系统和编译器而异，通常在1000到10000之间。解决方法包括：1.尾递归优化；2.尾调用；3.迭代实现。

C++ lambda 表达式是否支持递归？ Apr 17, 2024 pm 09:06 PM

是的，C++Lambda表达式可以通过使用std::function支持递归：使用std::function捕获Lambda表达式的引用。通过捕获的引用，Lambda表达式可以递归调用自身。

学信网如何查询自己的学历 Mar 28, 2024 pm 04:31 PM

学信网如何查询自己的学历？在学信网中是可以查询到自己的学历，很多用户都不知道如何在学信网中查询到自己的学历，接下来就是小编为用户带来的学信网查询自己学历方法图文教程，感兴趣的用户快来一起看看吧！学信网使用教程学信网如何查询自己的学历一、学信网入口：https://www.chsi.com.cn/二、网站查询：第一步：点击上方学信网地址，进入首页点击【学历查询】；第二步：在最新的网页中点击如下图箭头所示的【查询】；第三步：之后在新页面点击【的登陆学信档案】；第四步：在登陆页面输入信息点击【登陆】；

12306怎么查询历史购票记录查看历史购票记录的方法 Mar 28, 2024 pm 03:11 PM

　　12306订票app下载最新版是一款大家非常满意的出行购票软件，想去哪里就去那里非常方便，软件内提供的票源非常多，只需要通过实名认证就能在线购票，所有用户的出行车票机票都可以轻松买到，享受不同的优惠折扣。还能提前开启预约抢票，预约酒店、专车接送都是可以的，有了它想去哪里就去那里一键购票，出行更加简单方便，让大家的出行体验更舒服，现在小编在线详细为12306用户们带来查看历史购票记录的方法。　　1.打开铁路12306，点击右下角我的，点击我的订单　　2.在订单页面点击已支付。　　3.在已支付页

C++ 函数的递归实现：递归与非递归算法的比较分析？ Apr 22, 2024 pm 03:18 PM

递归算法通过函数自调用解决结构化的问题，优点是简洁易懂，缺点是效率较低且可能发生堆栈溢出；非递归算法通过显式管理堆栈数据结构避免递归，优点是效率更高且避免堆栈溢出，缺点是代码可能更复杂。选择递归或非递归取决于问题和实现的具体限制。

C++ 函数递归详解：递归在字符串处理中的应用 Apr 30, 2024 am 10:30 AM

递归函数是一种在字符串处理中反复调用自身来解决问题的技术。它需要一个终止条件以防止无限递归。递归在字符串反转和回文检查等操作中被广泛使用。

面向初学者的 C++ 递归指南：打造基础和培养直觉 May 01, 2024 pm 05:36 PM

递归是一种强大的技术，它允许函数调用自身来解决问题，在C++中，递归函数由两个关键要素构成：基本情况（确定递归何时停止）和递归调用（将问题分解为更小子问题）。通过理解基础知识并练习实战示例（如阶乘计算、斐波那契数列和二叉树遍历），您可以建立递归直觉，并自信地在代码中使用它。

如何在Linux中使用递归'ls” Mar 20, 2024 am 10:03 AM

在Linux系统中，“ls”命令是一个非常有用的工具，它提供了对当前目录中文件和文件夹的简洁概述。通过“ls”命令，您可以快速查看文件和文件夹的权限、属性等重要信息。虽然“ls”命令是一个基本的命令，但是通过结合不同的子命令和选项，它可以成为系统管理员和用户的重要工具。通过熟练使用“ls”命令及其各种选项，您可以更高效地管理文件系统，快速定位所需文件，以及执行各种操作。因此，“ls”命令不仅可以帮助您了解当前目录结构，还可以提高您的工作效率。比如，在Linux系统中，通过使用带有递归选项的"ls

See all articles

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

热AI工具

Undresser.AI Undress

AI Clothes Remover

Undress AI Tool

Clothoff.io

AI Hentai Generator

热门文章

热工具

记事本++7.3.1

SublimeText3汉化版

禅工作室 13.0.1

Dreamweaver CS6

SublimeText3 Mac版

热门话题