使用SQLSERVER 2005/2008 递归CTE查询树型结构的方法
时间:2024-01-27 15:13:10
下面是一个简单的Family Tree 示例:
DECLARE @TT TABLE (ID int,Relation varchar(25),Name varchar(25),ParentID int)
INSERT @TT SELECT 1,' Great GrandFather' , 'Thomas Bishop', null UNION ALL
SELECT 2,'Grand Mom', 'Elian Thomas Wilson' , 1 UNION ALL
SELECT 3, 'Dad', 'James Wilson',2 UNION ALL
SELECT 4, 'Uncle', 'Michael Wilson', 2 UNION ALL
SELECT 5, 'Aunt', 'Nancy Manor', 2 UNION ALL
SELECT 6, 'Grand Uncle', 'Michael Bishop', 1 UNION ALL
SELECT 7, 'Brother', 'David James Wilson',3 UNION ALL
SELECT 8, 'Sister', 'Michelle Clark', 3 UNION ALL
SELECT 9, 'Brother', 'Robert James Wilson', 3 UNION ALL
SELECT 10, 'Me', 'Steve James Wilson', 3
----------Query---------------------------------------
;WITH FamilyTree
AS(
SELECT *, CAST(NULL AS VARCHAR(25)) AS ParentName, 0 AS Generation FROM @TT
WHERE ParentID IS NULL
UNION ALL
SELECT Fam.*,FamilyTree.Name AS ParentName, Generation + 1 FROM @TT AS Fam
INNER JOIN FamilyTree ON Fam.ParentID = FamilyTree.ID
)SELECT * FROM FamilyTree
Output:
希望对您有帮助
Author: Petter Liu
标签:递归,CTE,树型结构
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
python中的测试框架
2023-02-16 03:59:08
python单例模式实例分析
2021-11-02 12:17:49
python编写接口测试文档(以豆瓣搜索为例)
2023-09-21 17:39:49
![](https://img.aspxhome.com/file/2023/2/62282_0s.png)
python 自动监控最新邮件并读取的操作
2023-02-04 12:58:51
javascript中的数字与字符串相加实例分析
2024-06-05 09:12:13
301转向代码合集
2008-03-20 10:12:00
PDO::beginTransaction讲解
2023-06-06 00:57:46
Python3使用xml.dom.minidom和xml.etree模块儿解析xml文件封装函数的方法
2023-12-19 22:42:41
利用go-zero在Go中快速实现JWT认证的步骤详解
2024-05-28 15:22:20
Python2.6版本中实现字典推导 PEP 274(Dict Comprehensions)
2022-04-13 02:53:50
pycharm 复制代码出现空格的解决方式
2023-08-27 12:36:37
![](https://img.aspxhome.com/file/2023/2/99232_0s.jpg)
Python基础知识学习之类的继承
2022-09-02 15:41:05
Vue3中简单实现动态添加路由
2023-07-02 16:58:45
![](https://img.aspxhome.com/file/2023/3/139863_0s.png)
学习XHTML和HTML之间的区别
2007-08-22 11:02:00
Python脚本实现定时任务的最佳方法
2021-09-20 10:41:35
斜角滑动门导航条 DIV+CSS
2008-07-19 15:45:00
![](https://img.aspxhome.com/file/UploadPic/20087/19/200871916554645s.gif)
利用python numpy+matplotlib绘制股票k线图的方法
2022-12-16 07:21:52
![](https://img.aspxhome.com/file/2023/0/67880_0s.jpg)
python使用turtle库绘制时钟
2022-09-13 07:06:51
![](https://img.aspxhome.com/file/2023/6/72856_0s.jpg)
Django处理多用户类型的方法介绍
2022-11-30 10:16:54
![](https://img.aspxhome.com/file/2023/3/85033_0s.png)
已解决卸载pip重新安装的方法
2023-09-27 22:08:02
![](https://img.aspxhome.com/file/2023/9/107059_0s.png)