如何删除完全重复的行 [英] How to delete completely duplicate rows

问题描述

说我的表中有重复的行，我的数据库设计是第三类： -

  Insert into tblProduct产品名称，产品名称，说明，类别）值（1，'Cinthol'，'化妆皂'，'肥皂'）; 
插入tblProduct（ProductId，ProductName，说明，类别）值（1，'Cinthol'，'化妆皂'，'肥皂'）; 
插入tblProduct（ProductId，ProductName，说明，类别）值（1，'Cinthol'，'化妆皂'，'肥皂'）; 
插入tblProduct（ProductId，ProductName，说明，类别）值（1，'Lux'，'化妆皂'，'肥皂'）; 
插入到tblProduct（ProductId，ProductName，说明，类别）值（1，'Crowning Glory'，'cosmetic soap'，'soap'）; 
插入tblProduct（ProductId，ProductName，Description，Category）值（2，'Cinthol'，'nice soap'，'soap'）; 
插入到tblProduct（ProductId，ProductName，说明，类别）值（3，'Lux'，'nice soap'，'soap'）; 
插入到tblProduct（ProductId，ProductName，说明，类别）值（3，'Lux'，'nice soap'，'soap'）; 
  

我希望每行只有一个实例应该存在于我的表中。因此，第2，第3和最后一行完全相同应该被删除。我可以为此写什么查询？可以不创建临时表来完成吗？只需一个查询？

提前感谢：）

解决方案

尝试这样 - 它将删除您的表中的所有重复项：

 ;与副本AS 
（
 SELECT 
 ProductID，ProductName，说明，类别，
 ROW_NUMBER（）OVER（PARTITION BY ProductID，ProductName 
 ORDER BY ProductID）'RowNum'
 FROM dbo.tblProduct 
 ）
 DELETE FROM duplicateates 
 WHERE RowNum> 1 
 GO 
 
 SELECT * FROM dbo.tblProduct 
 GO 
  

您的重复项目现在应该已经消失了：输出是：

  ProductID产品名称描述类别
 1 Cinthol化妆皂肥皂
 1 Lux化妆皂皂
 1加仑荣耀化妆皂皂
 2 Cinthol漂亮肥皂
 3 Lux好肥皂
  / pre> 
Say i have duplicate rows in my table and well my database design is of 3rd class :-
Insert Into tblProduct (ProductId,ProductName,Description,Category) Values (1,'Cinthol','cosmetic soap','soap');
Insert Into tblProduct (ProductId,ProductName,Description,Category) Values (1,'Cinthol','cosmetic soap','soap');
Insert Into tblProduct (ProductId,ProductName,Description,Category) Values (1,'Cinthol','cosmetic soap','soap');
Insert Into tblProduct (ProductId,ProductName,Description,Category) Values (1,'Lux','cosmetic soap','soap');
Insert Into tblProduct (ProductId,ProductName,Description,Category) Values (1,'Crowning Glory','cosmetic soap','soap');
Insert Into tblProduct (ProductId,ProductName,Description,Category) Values (2,'Cinthol','nice soap','soap');
Insert Into tblProduct (ProductId,ProductName,Description,Category) Values (3,'Lux','nice soap','soap');
Insert Into tblProduct (ProductId,ProductName,Description,Category) Values (3,'Lux','nice soap','soap');
I want only 1 instance of each row should be present in my table. Thus 2nd, 3rd and last row whcih are completely identical should be deleted. What query can i write for this? Can it be done without creating temp tables? Just in one single query?

Thanks in advance :)
解决方案
Try this - it will delete all duplicates from your table:
;WITH duplicates AS
(
    SELECT 
       ProductID, ProductName, Description, Category,
       ROW_NUMBER() OVER (PARTITION BY ProductID, ProductName
                          ORDER BY ProductID) 'RowNum'
    FROM dbo.tblProduct
)
DELETE FROM duplicates
WHERE RowNum > 1
GO

SELECT * FROM dbo.tblProduct
GO
Your duplicates should be gone now: output is:
ProductID   ProductName   DESCRIPTION        Category
   1          Cinthol         cosmetic soap      soap
   1          Lux             cosmetic soap      soap
   1          Crowning Glory  cosmetic soap      soap
   2          Cinthol         nice soap          soap
   3          Lux             nice soap          soap


                        
这篇关于如何删除完全重复的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！