SQL Server中使用递归好吗？

Question

我在SQL服务器中有一个表，它具有Item_ID、Item_ParentID的普通树形结构。 假设我想迭代并获取特定Item_ID（在任何级别）的所有子项。

递归似乎是这个问题的直觉候选，并且我可以编写一个SQL Server函数来完成此操作。

如果我的表有很多记录，这会影响性能吗？ 如何避免递归并简单地查询表？请提出任何建议。

- Jangwenyi

Answer 1

使用新的MS SQL 2005，您可以使用WITH关键字。 请查看此问题，特别是此答案。 对于Oracle，您可以使用CONNECT BY关键字生成分层查询(语法)。 据我所知，对于MySQL，您将不得不使用递归。 或者，您可以始终为记录的父->子关系构建缓存表。

- Ilya Kochetov

Answer 2

一般而言，使用SQL Server时可以通过迭代算法来完成通常需要递归的复杂操作。我曾经在Transact SQL中编写了一个XHTML解析器，效果出乎意料地好。我编写的代码美化程序是通过存储过程实现的。虽然不够优雅，有点像看水牛跳芭蕾，但它确实能用。

- Phil_Factor

---

我曾经在Transact-SQL中写过一个视频播放器！:-P - Jeffrey L Whitledge

你可以这样做，但是用Python/Ruby/C#/Java/Perl/LISP等编程语言编写XHTML解析器会更容易，而且性能可能会更好。 - Cervo

不开玩笑，它就在这里！ http://www.simple-talk.com/sql/t-sql-programming/getting-html-data-workbench/ - Eggs McLaren

我在T-SQL（SQL2K）中编写了一个词法分析器，用于生成数据库文档。它会从syscomments中提取定义，识别所有标记，然后为每个对象生成相互链接的HTML文件。在存储过程中单击表名，它会带您到表定义。非常有趣！最困难的部分实际上是解决8000字符限制的问题。 - Peter Radocchia

Answer 3

Joe Celko在SQL数据库中专门讲解树结构的书籍（<- 链接到亚马逊）。虽然您需要使用递归来建立模型，而且可能会存在性能问题，但根据您的具体问题，有其他方法可以对树结构进行建模，避免使用递归并提高性能。

- Tom H.

Answer 4

你正在使用SQL 2005吗？

如果是的话，你可以使用公共表达式来实现这个功能。大致如下：

;
with CTE (Some, Columns, ItemId, ParentId) as 
(
    select Some, Columns, ItemId, ParentId
    from myTable 
    where ItemId = @itemID
    union all
    select a.Some, a.Columns, a.ItemId, a.ParentId
    from myTable as a
    inner join CTE as b on a.ParentId = b.ItemId
    where a.ItemId <> b.ItemId
)
select * from CTE

- AlexCuse

Answer 5

你在使用递归时可能会遇到的问题是性能，因为它需要递归多少次才能返回结果。每个递归调用都是另一个独立的调用，需要将其合并到总结果中。

在SQL 2k5中，您可以使用公共表达式来处理这种递归：

WITH Managers AS 
( 
--initialization 
SELECT EmployeeID, LastName, ReportsTo  
FROM Employees 
WHERE ReportsTo IS NULL 
UNION ALL 
--recursive execution 
SELECT e.employeeID,e.LastName, e.ReportsTo 
FROM Employees e INNER JOIN Managers m  
ON e.ReportsTo = m.employeeID 
) 
SELECT * FROM Managers  

另一种解决方案是将层次结构展开成另一个表

Employee_Managers
ManagerId (主键，外键指向Employee表)
EmployeeId (主键，外键指向Employee表)

所有的父子关系都将存储在这个表中，因此如果经理1管理经理2管理员工3，则该表将如下所示：

ManagerId EmployeeId
1         2
1         3
2         1

这使得层次结构可以轻松查询：

select * from employee_managers em 
inner join employee e on e.employeeid = em.employeeid and em.managerid = 42

这将返回所有经理为42的员工。优点是更高的性能，但缺点是需要维护层次结构。

- jjacka

Answer 6

你根本不需要递归...... 注意，我将列更改为ItemID和ItemParentID以便输入...

DECLARE @intLevel INT
SET @intLevel = 1

INSERT INTO TempTable(ItemID, ItemParentID, Level) SELECT ItemID, ItemParentID, @intLevel WHERE ItemParentID IS NULL

当@intLevel < @TargetLevel时 BEGIN SET @intLevel = @intLevel + 1 INSERT INTO TempTable(ItemID, ItemParentID, Level) SELECt ItemID, ItemParentID, @intLevel WHERE ItemParentID IN (SELECT ItemID FROM TempTable WHERE Level = @intLevel-1) -- 如果没有插入行，则没有子项 IF @@ROWCOUNT = 0 BREAK END

SELECt ItemID FROM TempTable WHERE Level = @TargetLevel

- Cervo

Answer 7

或许需要更多的细节说明。

如果您有像您所描述的主从关系，那么一个简单的JOIN语句就可以得到您需要的内容，不是吗？

例如：

SELECT
  SOME_FIELDS
FROM
  MASTER_TABLE MT
 ,CHILD_TABLE CT
WHERE CT.PARENT_ID = MT.ITEM_ID

- kogus

Answer 8

对于子代，您不应该需要递归 - 您只查看直接下面的级别（即select * from T where ParentId = @parent） - 您只需要递归所有后代。

在SQL2005中，您可以使用以下方法获取后代：

with AllDescendants (ItemId, ItemText) as (
    select t.ItemId, t.ItemText 
        from [TableName] t
    where t.ItemId = @ancestorId
    union
    select sub.ItemId, sub.ItemText 
        from [TableName] sub
            inner join [TableName] tree
            on tree.ItemId = sub.ParentItemId
)

- Keith

---

我认为他的问题在于他想要“在特定级别上”进行操作。除非你存储了级别编号，否则你如何知道处于特定级别的内容而不去追溯根节点=级别1，根节点的子级别=级别2，孙子级别=级别3等等...并不需要使用递归...但可能存在多个父级节点。 - Cervo