我在SQL服务器中有一个表,它具有Item_ID、Item_ParentID的普通树形结构。 假设我想迭代并获取特定Item_ID(在任何级别)的所有子项。
递归似乎是这个问题的直觉候选,并且我可以编写一个SQL Server函数来完成此操作。
如果我的表有很多记录,这会影响性能吗? 如何避免递归并简单地查询表?请提出任何建议。
我在SQL服务器中有一个表,它具有Item_ID、Item_ParentID的普通树形结构。 假设我想迭代并获取特定Item_ID(在任何级别)的所有子项。
递归似乎是这个问题的直觉候选,并且我可以编写一个SQL Server函数来完成此操作。
如果我的表有很多记录,这会影响性能吗? 如何避免递归并简单地查询表?请提出任何建议。
一般而言,使用SQL Server时可以通过迭代算法来完成通常需要递归的复杂操作。我曾经在Transact SQL中编写了一个XHTML解析器,效果出乎意料地好。我编写的代码美化程序是通过存储过程实现的。虽然不够优雅,有点像看水牛跳芭蕾,但它确实能用。
你正在使用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
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的员工。优点是更高的性能,但缺点是需要维护层次结构。
你根本不需要递归...... 注意,我将列更改为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
或许需要更多的细节说明。
如果您有像您所描述的主从关系,那么一个简单的JOIN语句就可以得到您需要的内容,不是吗?
例如:
SELECT
SOME_FIELDS
FROM
MASTER_TABLE MT
,CHILD_TABLE CT
WHERE CT.PARENT_ID = MT.ITEM_ID
对于子代,您不应该需要递归 - 您只查看直接下面的级别(即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
)