如何在MSSQL 2005中创建递归查询? [英] How do I create a recursive query in MSSQL 2005?
本文介绍了如何在MSSQL 2005中创建递归查询?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
让我们说下面的表:
CustomerID ParentID Name
========== ======== ====
1 null John
2 1 James
3 2 Jenna
4 3 Jennifer
5 3 Peter
6 5 Alice
7 5 Steve
8 1 Larry
想要在一个查询中检索所有James的后代(Jenna,Jennifer,Peter,Alice,Steve)。
在SQL Server 2005上,您可以使用
I want to retrieve in one query all the descendants of James (Jenna,Jennifer,Peter, Alice, Steve). Thanks, Pablo.
推荐答案
://www.4guysfromrolla.com/webtech/071906-1.shtmlrel =noreferrer> CTEs(通用表格表达式):
On SQL Server 2005 you can use CTEs (Common Table Expressions) :
with Hierachy(CustomerID, ParentID, Name, Level)
as
(
select CustomerID, ParentID, Name, 0 as Level
from Customers c
where c.CustomerID = 2 -- insert parameter here
union all
select c.CustomerID, c.ParentID, c.Name, ch.Level + 1
from Customers c
inner join Hierachy ch
on c.ParentId = ch.CustomerID
)
select CustomerID, ParentID, Name
from Hierachy
where Level > 0
这篇关于如何在MSSQL 2005中创建递归查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文