sql: 删除一个子树表(id, Parentid) ，删除一个项目及其所有子项

Question

我有一张这样的 table

foo(id, parentId) 
-- there is a FK constraint from parentId to id

我需要删除一个项目及其所有 child 和 child 的 child 等。有人知道怎么做吗？

Answer 1

据我所知，SQL SERVER 不喜欢分层关系的级联删除。因此，您可以同时执行 CTE (如 Oded 提到的)或具有递归触发器的解决方案(类似于 this )。但我认为 CTE 更容易。

看，这是使用 CTE 的解决方案:

CREATE PROC deleteFoo 
@id bigint
as
WITH Nodes ([Id], [ParentId], [Level]) 
AS (
    SELECT  F.[Id], F.[ParentId], 0 AS [Level]
    FROM    [dbo].Foo F
    WHERE   F.[Id] = @id

    UNION ALL

    SELECT  F.[Id], F.[ParentId], N.[Level] + 1
    FROM    [dbo].Foo F
        INNER JOIN Nodes N ON N.[Id] = F.[ParentId]
)

DELETE
FROM    Foo
WHERE   [Id] IN (
    SELECT  TOP 100 PERCENT N.[Id] 
    FROM    Nodes N
    ORDER BY N.[Level] DESC
)

首先，我们定义递归 CTE，然后从 [Foo] 表中从最子记录(最高 Level)开始删除记录；因此，顶部节点将在最后一轮被删除)。