Question

我有一个称为客户信息数据库、客户目录的客户表。我有一份命令表,其中提到客户信息表。

我需要找到并删除在订单表上持有订单的客户登记册的所有重复记录。

这就是我是如何在客户中发现我的重复,但我不知道如何通过命令表过滤,然后删除额外记录:

SELECT Name, CustomerNumber, COUNT(*) As DupeCount
FROM StagingCustomers 
WHERE ManufacturerID=15
GROUP BY Name, CustomerNumber
HAVING COUNT(CustomerNumber) > 1
ORDER BY CustomerNumber

Answer 1

添加<条码>EXISTS。 a. 在无关系的情况下检查任何记录的条款:

SELECT Name, CustomerNumber, COUNT(*) As DupeCount
FROM StagingCustomers S
WHERE ManufacturerID=15
AND NOT EXISTS (SELECT 1 from Orders WHERE CustomerID = S.CustomerID)
GROUP BY Name, CustomerNumber
HAVING COUNT(CustomerNumber) > 1
ORDER BY CustomerNumber

http://www.un.org。

删除以下记录的例子。这将为符合上述标准的每份记录设定<代码>。为了更明确地回答,你需要回答您的布局和关系。

UPDATE  S
SET IsDeleted = 1
FROM StagingCustomers S
WHERE ManufacturerID=15
AND NOT EXISTS (SELECT 1 from Orders WHERE CustomerID = S.CustomerID)
AND CustomerID NOT IN (SELECT CustomerNumber, MIN(CustomerID)
                       FROM StagingCustomers
                       GROUP BY CustomerNumber)
GROUP BY Name, CustomerNumber
HAVING COUNT(CustomerNumber) > 1
ORDER BY CustomerNumber

Answer 2

这样做也可能是:

SELECT Name, CustomerNumber, COUNT(*) As DupeCount
FROM StagingCustomers AS SC
LEFT JOIN ORDERS AS OD ON SC.CustomerID = OD.CustomerID
WHERE OD.CustomerID IS NULL AND ManufacturerID = 15
GROUP BY Name, CustomerNumber
HAVING COUNT(CustomerNumber) > 1
ORDER BY CustomerNumber

NOTE: Did not test the Code, may need to tweak.

友情链接