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

一种方法是使用 INNER JOIN :

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 Server 2005+,则可以使用 CTE :

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

你能按订单号删除吗?如果是这样的话,你可以从技术上尝试

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

这将选择上面包含的同一个选择列表,但实际上只选择订单ID,从而返回要删除的订单ID列表

然而,正如所指出的,只有当order\u id是主键时,这才有效!伊普,我承认不完美