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

MySQL的位置：數據庫和編程Apr 13, 2025 am 12:18 AM

MySQL在數據庫和編程中的地位非常重要，它是一個開源的關係型數據庫管理系統，廣泛應用於各種應用場景。 1）MySQL提供高效的數據存儲、組織和檢索功能，支持Web、移動和企業級系統。 2）它使用客戶端-服務器架構，支持多種存儲引擎和索引優化。 3）基本用法包括創建表和插入數據，高級用法涉及多表JOIN和復雜查詢。 4）常見問題如SQL語法錯誤和性能問題可以通過EXPLAIN命令和慢查詢日誌調試。 5）性能優化方法包括合理使用索引、優化查詢和使用緩存，最佳實踐包括使用事務和PreparedStatemen

MySQL：從小型企業到大型企業Apr 13, 2025 am 12:17 AM

MySQL適合小型和大型企業。 1)小型企業可使用MySQL進行基本數據管理，如存儲客戶信息。 2)大型企業可利用MySQL處理海量數據和復雜業務邏輯，優化查詢性能和事務處理。

幻影是什麼讀取的，InnoDB如何阻止它們（下一個鍵鎖定）？Apr 13, 2025 am 12:16 AM

InnoDB通過Next-KeyLocking機制有效防止幻讀。 1）Next-KeyLocking結合行鎖和間隙鎖，鎖定記錄及其間隙，防止新記錄插入。 2）在實際應用中，通過優化查詢和調整隔離級別，可以減少鎖競爭，提高並發性能。

mysql：不是編程語言，而是...Apr 13, 2025 am 12:03 AM

MySQL不是一門編程語言，但其查詢語言SQL具備編程語言的特性：1.SQL支持條件判斷、循環和變量操作；2.通過存儲過程、觸發器和函數，用戶可以在數據庫中執行複雜邏輯操作。

MySQL：世界上最受歡迎的數據庫的簡介Apr 12, 2025 am 12:18 AM

MySQL是一種開源的關係型數據庫管理系統，主要用於快速、可靠地存儲和檢索數據。其工作原理包括客戶端請求、查詢解析、執行查詢和返回結果。使用示例包括創建表、插入和查詢數據，以及高級功能如JOIN操作。常見錯誤涉及SQL語法、數據類型和權限問題，優化建議包括使用索引、優化查詢和分錶分區。

MySQL的重要性：數據存儲和管理Apr 12, 2025 am 12:18 AM

MySQL是一個開源的關係型數據庫管理系統，適用於數據存儲、管理、查詢和安全。 1.它支持多種操作系統，廣泛應用於Web應用等領域。 2.通過客戶端-服務器架構和不同存儲引擎，MySQL高效處理數據。 3.基本用法包括創建數據庫和表，插入、查詢和更新數據。 4.高級用法涉及復雜查詢和存儲過程。 5.常見錯誤可通過EXPLAIN語句調試。 6.性能優化包括合理使用索引和優化查詢語句。

為什麼要使用mysql？利益和優勢Apr 12, 2025 am 12:17 AM

選擇MySQL的原因是其性能、可靠性、易用性和社區支持。 1.MySQL提供高效的數據存儲和檢索功能，支持多種數據類型和高級查詢操作。 2.採用客戶端-服務器架構和多種存儲引擎，支持事務和查詢優化。 3.易於使用，支持多種操作系統和編程語言。 4.擁有強大的社區支持，提供豐富的資源和解決方案。

描述InnoDB鎖定機制（共享鎖，獨家鎖，意向鎖，記錄鎖，間隙鎖，下一鍵鎖）。Apr 12, 2025 am 12:16 AM

InnoDB的鎖機制包括共享鎖、排他鎖、意向鎖、記錄鎖、間隙鎖和下一個鍵鎖。 1.共享鎖允許事務讀取數據而不阻止其他事務讀取。 2.排他鎖阻止其他事務讀取和修改數據。 3.意向鎖優化鎖效率。 4.記錄鎖鎖定索引記錄。 5.間隙鎖鎖定索引記錄間隙。 6.下一個鍵鎖是記錄鎖和間隙鎖的組合，確保數據一致性。

See all articles