logo标签列表

在SQL表中删除分层数据

sqlsql-server-2005
17
我有一张包含分层数据的表格。
有一个“ParentId”列，它存储了其父对象的“ID”(关键列)。
当删除一行时，我想要删除所有子项（所有嵌套级别）。
如何实现？
谢谢。
8个回答
11

在 SQL Server 中：使用递归查询。给定 CREATE TABLE tmp(Id int, Parent int)，使用

WITH x(Id) AS (
    SELECT @Id
    UNION ALL
    SELECT tmp.Id
      FROM tmp
      JOIN x ON tmp.Parent = x.Id
)
DELETE tmp
  FROM x
  JOIN tmp ON tmp.Id = x.Id
6

添加外键约束。以下示例适用于MySQL（语法参考）：

ALTER TABLE yourTable
ADD CONSTRAINT makeUpAConstraintName
FOREIGN KEY (ParentID) REFERENCES yourTable (ID)
ON DELETE CASCADE;

这将在数据库级别操作，数据库管理系统将确保一旦删除一行，所有引用该行的行也将被删除。

2
SQL Server 2005不支持自引用级联删除。当您尝试删除一行带有“子”行时，将会出现错误提示。 - Matt Hamilton
1
作者在我撰写答案时并未指定任何数据库管理系统。为了参考起见，我将保留它。 - soulmerge
好的，那就可以了。感谢您的澄清。 - Matt Hamilton
ON DELETE CASCADE 在 Oracle 中也适用。在我看来，这是最好的答案（如果您的 DBMS 支持它）。 - Jim Ferrans
澄清一下，我只在主-从关系只有一层且模型分为 两个 表的情况下使用过这种方法。 - Jim Ferrans
4

当行数不太大时，erikkallen的递归方法可行。

这里有一种替代方案，它使用临时表来收集所有子项：

create table #nodes (id int primary key)
insert into #nodes (id) values (@delete_id)
while @@rowcount > 0
    insert into #nodes 
    select distinct child.id 
    from table child
    inner join #nodes parent on child.parentid = parent.id
    where child.id not in (select id from #nodes)

delete
from table
where id in (select id from #nodes)

从@delete_id所在的行开始，向下降序排列。where语句是为了防止递归；如果你确定没有递归，可以省略该语句。

我在 SQL 方面不是很强，所以我想问一下： 为什么需要这样写："select id from table where id = @delete_id"，不能直接使用 @delete_id 作为值吗？ - markiz
如果这比递归CTE表现更好，我会非常惊讶。 - erikkallen
3

取决于你如何存储你的层次结构。如果你只有ParentID，那么这可能不是你采取的最有效方法。为了方便子树操作，你应该有一个额外的列Parents，它将存储所有父级ID，例如：

/1/20/25/40

通过这种方式，您可以轻松地获取所有子节点：

where Parents like @NodeParents + '%'

第二种方法
除了仅使用ParentID外，您还可以具有left和right值。以这种方式进行的插入速度较慢，但选择操作非常快。特别是在处理子树节点时... http://en.wikipedia.org/wiki/Tree_traversal

第三种方法
如果您使用SQL 2005+，请检查递归CTE。

第四种方法
如果您使用SQL 2008，请检查HierarchyID类型。它为您的情况提供了足够的可能性。 http://msdn.microsoft.com/en-us/magazine/cc794278.aspx

不，我不想在一列中存储整个父级链，因为涉及到不断变化的父级。而且很难跟踪所有这些。 现在的做法不能吗？ - markiz
你的层次数据的主要操作是什么？是插入、更新还是读取？ - Robert Koritnik
我倾向于同意第一种方法 - 我们有层次数据的表格，我们正在使用相同的方法处理它。这有助于摆脱子级，并且如果您需要对树进行基于路径的处理（例如必须快速返回父项的所有子项以进行计算），则也会有所帮助。最初，我们尝试使用触发器来维护此内容，但是当添加大量数据时，我们真的发现性能影响是禁止的。 - John Christensen
如果你正在使用SQL Server 2005，第三种方法可能是你绝对应该考虑的。这样你就不必维护一个具有多个属性的Parents列，这很麻烦。 - J. Polfer
2

像这样向表添加触发器：

创建一个在删除时触发的触发器，如下所示：

create trigger TD_MyTable on myTable for delete as -- 删除一级子项 delete M from deleted D inner join myTable M on D.ID = M.ID

每次删除都会调用同一张表上的删除操作，不断调用触发器。请查阅在线书籍以获取其他规则。可能会有触发器嵌套次数限制。

ST

SQL SERVER 2005 Express中有哪些触发器可用？ - markiz
我相信它们是可以的，但你必须自己在 Express 中编写它们。没有向导可用。 - souLTower
我猜触发器会起作用，但触发器的问题在于它会在每次删除时被激活，即使在我只想删除一行的情况下也是如此... - markiz
0

这取决于你使用的数据库。如果你使用的是Oracle，你可以像这样做：

DELETE FROM Table WHERE ID IN (
  SELECT ID FROM Table
  START WITH ID = id_to_delete
  CONNECT BY PRIOR.ID = ParentID
)

预计时间：

如果没有使用CONNECT BY，那么情况会变得有些棘手。正如其他人所建议的那样，触发器或级联删除约束可能是最简单的方法。

我正在使用 MS SQL SERVER 2005 express。 - markiz
0
是的。最大级别深度大约为8、9。无论如何，我不认为我会使用触发器，因为我不需要在数据库上运行每个删除命令时激活触发器。 - markiz
-1
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接