从SQL表中删除重复行（基于多列值）

Question

从SQL表中删除重复行（基于多列值）

42

我有以下的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行属于同一客户（测试客户），该客户具有不同的ShipToCodes：1、2和3。第二个客户（ARACODE客户）情况类似。每个普通客户和普通客户-2仅有1条记录，带有单个ShipToCode。

现在，我想在此表上查询结果，其中每个客户只有1条记录。因此，对于任何客户，如果有多条记录，我希望保留具有最高ShipToCode值的记录。

我尝试过各种方法：

（1）我可以轻松获取表中仅有一条记录的客户列表。

（2）使用以下查询，我能够获取所有在表中拥有多条记录的客户列表。

[Query-1]

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

(3) 现在，为了为上述查询返回的每个记录选择适当的ShipToCode，我无法弄清如何迭代所有返回的记录。

如果我做这样的事情：

[Query-2]

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

那么我可以得到 (00-1234567-Test Customer) 对应的适当记录。如果我能在上述查询（查询2）中使用查询1的所有结果，那么我就可以获得拥有多个记录的客户的所需单个记录。这可以与第一点的结果结合起来实现所需的最终结果。

再次强调，这可能比我正在遵循的方法更容易。请告诉我如何做到这一点。

[注意：我必须仅使用SQL查询来完成此操作。我不能使用存储过程，因为我最终要使用'Scribe Insight'执行此操作，该工具只允许我编写查询语句。]

- Vikram

可能是如何在SQL Server中删除重复行？的重复问题。 - Tab Alleman

5个回答

11

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

您提到要删除重复项，如果想要删除，只需执行以下操作：

;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是可选的，但用于针对给定字段或字段组中的每个值重新开始编号，例如：如果您使用PARTITION BY Some_Date，则对于每个唯一日期值，编号将从1重新开始计数。当然，ORDER BY用于定义计数方式，在ROW_NUMBER()函数中是必需的。

- Hart CO

非常感谢您的回答和详细的解释！ :) - Vikram

1

稍作更正：对于我在提问中的特定示例，应该是“partition by ARDivisionNo，CustomerNo”，而不是“partition by CustomerNo”。 - Vikram

@Vikram 已相应更新。 - Hart CO

7

您没有指定SQL Server的版本，但是ROW_NUMBER可能被支持：

select *
from
 (
  select ...
     ,row_number() 
      over (partition by ARDivisionNo, CustomerNo
            order by ShipToCode desc) as rn 
  from tab
 ) as dt
where rn = 1

- dnoeth

1

非常感谢您的回答！ :) - Vikram

7

使用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

- Giorgi Nakeuri

非常感谢您的回答！ :) - Vikram

1

稍作更正：对于我在提问中的特定示例，应该是 partition by ARDivisionNo, CustomerNo 而不是 partition by CustomerNo。 - Vikram

0

你还可以使用分组。

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

- Ellen

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- HaveNoDisplayName · Accepted Answer

SQL FIDDLE示例

1) 使用CTE根据ARDivisionNo和CustomerNo获取每个客户的最大船舶代码值记录。

WITH cte AS (
  SELECT*, 
     row_number() OVER(PARTITION BY ARDivisionNo, CustomerNo ORDER BY ShipToCode desc) AS [rn]
  FROM t
)
Select * from cte WHERE [rn] = 1

2) 要删除记录，请使用Delete查询而不是Select，并将Where子句更改为rn > 1。示例SQL FIDDLE

WITH cte AS (
  SELECT*, 
     row_number() OVER(PARTITION BY ARDivisionNo, CustomerNo ORDER BY ShipToCode desc) AS [rn]
  FROM t
)
Delete from cte WHERE [rn] > 1;

select * from t;