如何在没有SQL Server中所选语句的情况下从表中删除所有记录

Question

SELECT DISTINCT Panel_ID,Delivery_Date 
FROM SalesFact_Final_History_bk_04MAY a
WHERE (SELECT COUNT(DISTINCT(delivery_date))
       FROM SalesFact_Final_History_bk_04MAY b
       WHERE a.Delivery_Date<=b.Delivery_Date
       AND a.Panel_ID=b.Panel_ID
) < 3 
ORDER BY Panel_ID,Delivery_Date DESC

在这里，我正在从历史表中选择每个面板ID的最新两个日期。我想删除所有信息，而无需删除此选定的语句信息。我该怎么做？

Answer 1

请尝试以下...

DELETE SalesFact_Final_History_bk_04MAY
FROM SalesFact_Final_History_bk_04MAY
INNER JOIN ( SELECT ROW_NUMBER() OVER ( PARTITION BY Panel_ID ORDER BY Delivery_Date DESC ) AS recordNumber,
                    Panel_ID AS Panel_ID,
                    Delivery_Date AS Delivery_Date
             FROM ( SELECT Panel_ID AS Panel_ID,
                           Delivery_Date AS Delivery_Date
                    FROM SalesFact_Final_History_bk_04MAY
                    GROUP BY Panel_ID,
                             Delivery_Date
                  ) AS uniquePanel_IDDelivery_Date
           ) AS recordsToKeepFinder ON SalesFact_Final_History_bk_04MAY.Panel_ID <> recordsToKeepFinder.Panel_ID
                                   AND SalesFact_Final_History_bk_04MAY.Delivery_Date <> recordsToKeepFinder.Delivery_Date
                                   AND recordNumber > 2;

此语句获取由您提供的语句生成的列表(在此处订购，在此不用有用(，并使用SalesFact_Final_History_bk_04MAY执行INNER JOIN，以使得SalesFact_Final_History_bk_04MAY的每个记录都不执行在SELECT语句的结果中保留了匹配的Panel_ID和Delivery_Date，并且省略了 die 匹配的每个记录。然后从SalesFact_Final_History_bk_04MAY删除SELECT语句保留的每个记录。

如果您有任何疑问或评论，请随时发表评论。

Answer 2

这是解决方案：您可以尝试一下：

DELETE a
FROM SalesFact_Final_History_bk_04MAY a
WHERE (SELECT COUNT(DISTINCT(delivery_date))
        FROM SalesFact_Final_History_bk_04MAY b
        WHERE a.Delivery_Date<=b.Delivery_Date
        AND a.Panel_ID=b.Panel_ID
) >= 3

Answer 3

Try this code 
DELETE FROM SalesFact_Final_History_bk_04MAY WHERE Panel_ID NOT IN (
SELECT DISTINCT Panel_ID
FROM SalesFact_Final_History_bk_04MAY a
WHERE (SELECT COUNT(DISTINCT(delivery_date))
FROM SalesFact_Final_History_bk_04MAY b
WHERE a.Delivery_Date<=b.Delivery_Date
AND a.Panel_ID=b.Panel_ID
) < 3 
ORDER BY PANEL_ID,DELIVERY_DATE DESC
)