从SQL表中删除重复行(基于多列的值)

Question

我有以下SQL表：

AR_Customer_ShipTo

+--------------+------------+-------------------+------------+
| ARDivisionNo | CustomerNo |   CustomerName    | ShipToCode |
+--------------+------------+-------------------+------------+
|           00 | 1234567    | Test Customer     |          1 |
|           00 | 1234567    | Test Customer     |          2 |
|           00 | 1234567    | Test Customer     |          3 |
|           00 | ARACODE    | ARACODE Customer  |          1 |
|           00 | ARACODE    | ARACODE Customer  |          2 |
|           01 | CBE1EX     | Normal Customer   |          1 |
|           02 | ZOCDOC     | Normal Customer-2 |          1 |
+--------------+------------+-------------------+------------+

(ARDivisionNo, CustomerNo,ShipToCode)构成此表的主键。

如果您注意到前3行属于同一个客户(Test )，它具有不同的ShipToCodes: 1、2和3。第二个客户(ARACODE )的情况类似。每个普通客户和普通客户-2只有一个ShipToCode的记录。

现在，我希望在这个表上获得结果查询，在这个表中，每个客户只有1条记录。因此，对于任何有超过1条记录的客户，我希望保留ShipToCode的最高价值的记录。

我尝试了很多种方法：

(1)我很容易就能得到表中只有一份记录的顾客名单。

(2)通过以下查询，我可以得到表中有多个记录的所有客户的列表。

查询-1

SELECT ARDivisionNo, CustomerNo
FROM AR_Customer_ShipTo 
GROUP BY ARDivisionNo, CustomerNo
HAVING COUNT(*) > 1;

(3)现在，为了为上述查询返回的每条记录选择合适的ShipToCode，我无法弄清楚如何遍历上述查询返回的所有记录。

如果我做这样的事：

查询-2

SELECT TOP 1 ARDivisionNo, CustomerNo, CustomerName, ShipToCode  
FROM AR_Customer_ShipTo 
WHERE ARDivisionNo = '00' and CustomerNo = '1234567'
ORDER BY ShipToCode DESC

然后，我可以得到适当的记录(00-1234567-测试客户)。因此，如果我可以在上面的查询(查询-2)中使用查询-1的所有结果，那么我可以为拥有多个记录的客户获得所需的单个记录。这可以与点(1)的结果相结合，以达到预期的最终结果。

同样，这比我所遵循的方法要容易得多。请让我知道我该怎么做。

注意:我只能使用SQL查询来完成这个任务。我不能使用存储过程，因为我最终将使用'Scribe‘来执行这个任务，它只允许我编写查询。

Answer 1

ROW_NUMBER()在这方面做得很好：

;WITH cte AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY ARDivisionNo,CustomerNo ORDER BY ShipToCode DESC) AS RN 
              FROM AR_Customer_ShipTo
              )
SELECT * 
FROM  cte
WHERE RN = 1

您提到删除副本，如果您想要DELETE，可以简单地：

;WITH cte AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY ARDivisionNo,CustomerNo ORDER BY ShipToCode DESC) AS RN 
              FROM AR_Customer_ShipTo
              )
DELETE cte
WHERE RN > 1

ROW_NUMBER()函数为每一行分配一个数字。PARTITION BY是可选的，但用于开始对给定字段或字段组中的每个值进行重新编号，即:如果您对每个唯一的日期值重新编号，编号将从1开始。当然，ORDER BY用于定义计数应该如何进行，并且在ROW_NUMBER()函数中是必需的。

Answer 2

使用row_number函数：

SELECT * FROM(
              SELECT ARDivisionNo, CustomerNo, CustomerName, ShipToCode,
              row_number() over(partition by CustomerNo order by ShipToCode desc) rn
              FROM AR_Customer_ShipTo) t
WHERE rn = 1

Answer 3

您也可以使用分组。

SELECT ARDivisionNo, 
       CustomerNo,
       max(ShipToCode) as ShipToCode  
FROM AR_Customer_ShipTo 
GROUP BY ARDivisionNo, CustomerNo