删除包含重复数据的一列

Question

考虑以下内容：

ProductID  Supplier
---------  --------
111        Microsoft
112        Microsoft
222        Apple Mac
222        Apple
223        Apple

在这个例子中，产品222被重复列出，因为提供数据的供应商有两个名称。

我有成千上万个产品的数据。我该如何删除重复的产品或选择单个结果 - 比如使用SELECT TOP 1等自连接方式？

谢谢！

- Warren

---

SQL Server 的哪个版本？ - OMG Ponies

你可以用一次DELETE完成。只有一件事，先备份它。 - Popnoodles

Answer 1

DELETE  a
FROM    tableName a
        LEFT JOIN
        (
            SELECT  Supplier, MIN(ProductID) min_ID
            FROM    tableName
            GROUP   BY Supplier
        ) b ON  a.supplier = b.supplier AND
                a.ProductID = b.min_ID
WHERE   b.Supplier IS NULL

• SQLFiddle演示

或者如果您想删除具有多个产品的productID

WITH cte 
AS
(
    SELECT  ProductID, Supplier,
            ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY Supplier) rn
    FROM    tableName
)
DELETE FROM cte WHERE rn > 1

• SQLFiddle演示

- John Woo

Answer 2

我认为您想要做以下事情：

我认为您想要做以下事情：

select t.*
from (select t.*,
             row_number() over (partition by product_id order by (select NULL)) as seqnum
      from t
     ) t
where seqnum = 1

该语句为每个产品选择一行。

要删除除一行之外的所有行，您可以使用相同的方法：

with todelete (
      (select t.*,
               row_number() over (partition by product_id order by (select NULL)) as seqnum
        from t
      )
delete from to_delete where seqnum > 1

- Gordon Linoff

---

Gordon，你的选择语句完美地满足了我的需求 - 谢谢。有没有办法使用组的最大序列号来有效地获取最新的结果？再次感谢！ - Warren

Answer 3

;WITH Products_CTE AS
    (
    SELECT ProductID, Supplier,
        ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY <some value>) as rn
    FROM PRODUCTS
    )

SELECT *
FROM Products_CTE
WHERE rn = 1

“some value”将成为决定保留哪个版本供应商的关键。如果您想要第一个供应商实例，则可以使用DateAdded列（如果存在）。

- bobs