删除查询中找到的所有数据[重复]

Question

我正在做一个数据库项目，为了理智起见，我想在测试其他特性时从数据库中删除所有问题数据。

SELECT Order_ID, Part_Number, COUNT(*)
FROM Temp_A
GROUP BY Order_ID, Part_Number
HAVING COUNT(*) > 1

DELETE FROM Temp_A
WHERE IN (/*MY QUERY*/)

Answer 1

一种方法是使用内部联接:

DELETE A
FROM Temp_A A
INNER JOIN (SELECT Order_ID, Part_Number, COUNT(*)
            FROM Temp_A
            GROUP BY Order_ID, Part_Number
            HAVING COUNT(*) > 1) B
    ON A.Order_ID = B.Order_ID
    AND A.Part_Number = B.Part_Number
;

如果使用的是SQL Server2005+，则可以使用CTE:

WITH CTE AS
(
    SELECT  *,
            N = COUNT(*) OVER(PARTITION BY OrderID, Part_Number)
    FROM Temp_A
)
DELETE FROM CTE
WHERE N > 1
;