主页 / dba / 问题 / 134027
Accepted
Greg
Asked: 2016-04-01 21:22:55 +0800 CST

如何使用子查询进行 SQL 删除

  • 772

我们的一位开发人员添加了以下代码，用于从表中删除重复记录：

DELETE  SubQuery

FROM
(
    SELECT  ID
            ,FK1
            ,FK2
            ,CreatedDateTime
            ,ROW_NUMBER() OVER(PARTITION BY FK1, FK2 ORDER BY CreatedDateTime) AS RowNumber

    FROM    Table
)
AS SubQuery

WHERE   RowNumber > 1

在查看代码时，我认为它不起作用，但是在我们的测试环境（SQL 2014）中测试它表明它可以！

SQL 如何知道解决子查询并从中删除记录table？

sql-server sql-server-2014

2 个回答

  1. Best Answer

    subquery您在代码中拥有的称为派生表。它不是基表，而是在查询运行期间“存在”的表。就像视图（也称为查看的表）一样——在最近的版本中， CTE是另一种在查询中“定义”表的第四种方式——它们在很多方面都类似于表。您可以select从他们那里，您可以在其他表中使用它们from或将join它们用于其他表（无论是否为基础！）。

    在某些 DBMS 中，（并非所有 DBMS 都以相同的方式实现）这些表/视图是可更新的。而“可更新”意味着我们也可以update，insert进入或delete离开它们。

    虽然有限制，但这是意料之中的。想象一下，如果subquery是 2 个（或 17 个表）的连接。那会是什么delete意思呢？（应该从哪些表中删除行？）可更新视图是一件非常复杂的事情。最近有一本（2012 年）完全关于这个主题的书，由著名的关系理论专家 Chris Date 撰写：查看更新和关系理论。

    当派生表（或视图）是一个非常简单的查询，比如它只有一个基表（可能受 a 限制WHERE）并且没有GROUP BY，那么派生表的每一行对应于底层基表中的一行，所以它是容易*从中更新、插入或删除。

    当子查询中的代码更复杂时，这取决于派生表/视图的行是否可以跟踪/解析为来自基础基表之一的行。

    对于 SQL Server，您可以在 MSDN 中的可更新视图段落中阅读更多信息：CREATE VIEW。

    可更新视图

    只要满足以下条件，就可以通过视图修改基础基表的数据：

    • 任何修改，包括UPDATE、INSERT和DELETE语句，都必须仅引用一个基表中的列。

    • 视图中被修改的列必须直接引用表列中的基础数据。这些列不能以任何其他方式派生，例如通过以下方式：

    • 聚合函数：AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, 和VARP.

    • 一个计算。无法从使用其他列的表达式计算该列。使用集合运算符UNION、UNION ALL、CROSSJOIN、EXCEPT和形成的列INTERSECT 相当于计算并且也不可更新。

    • 正在修改的列不受GROUP BY、HAVING或DISTINCT子句的影响。

    • TOP不与WITH CHECK OPTION子句一起在视图的 select_statement 中的任何地方使用。

    前面的限制适用于FROM视图子句中的任何子查询，就像它们适用于视图本身一样。通常，数据库引擎必须能够明确地跟踪从视图定义到一个基表的修改。

    实际上delete比 更容易，更简单update。SQL Server 只需要主键或其他方式来标识要删除基表的哪些行。对于update，还有一个额外的（相当明显的）限制，我们不能更新计算列。您可以尝试修改查询以进行更新。更新CreatedDateTime可能会正常工作，但尝试更新计算RowNumber列会引发错误。而且insert更复杂，因为我们必须为基表中没有DEFAULT约束的所有列提供值。

    • 14

  2. 查看查询计划时很容易看到。在您的情况下，该计划仅包含一个额外的 Segment 和 Sequence Project 运算符来处理行号。仅当 SQL Server 实际可以解析基础表时，这种类型的操作才有效。

    完全支持从子查询和 CTE 中删除并且非常有效，特别是对于删除重复项。我似乎还记得在旧版本的 SQL Server 上使用过它。

    更多在我的旧博客文章中。

    • 4

相关问题