sql查询只删除一个重复的行

I've a table with some duplicate rows in it. I want to delete only one duplicate row.

我有一个表中有一些重复的行。我想只删除一个重复的行。

For example I'v 9 duplicate rows so should delete only one row and should show 8 remaining rows.

例如,我有9个重复的行,所以应该只删除一行,并且应该显示8个剩余的行。

example

date calling called duration timestampp

日期调用称为持续时间戳

2012-06-19 10:22:45.000 165 218 155 1.9 121
2012-06-19 10:22:45.000 165 218 155 1.9 121
2012-06-19 10:22:45.000 165 218 155 1.9 121
2012-06-19 10:22:45.000 165 218 155 1.9 121

from above date should delete only one row and should show 3 rows

从上面的日期应该只删除一行,并应显示3行

2012-06-19 10:22:45.000 165 218 155 1.9 100
2012-06-19 10:22:45.000 165 218 155 1.9 100
2012-06-19 10:22:45.000 165 218 155 1.9 100

from above date should delete only one row and should show 2 rows

从上面的日期应该只删除一行,并应显示2行

How can I do this?

我怎样才能做到这一点?

6 个解决方案

#1

This solution allows you to delete one row from each set of duplicates (rather than just handling a single block of duplicates at a time):

此解决方案允许您从每组重复项中删除一行(而不是一次只处理一个重复项块):

;WITH x AS 
(
  SELECT [date], rn = ROW_NUMBER() OVER (PARTITION BY 
    [date], calling, called, duration, [timestamp]
    ORDER BY [date])
  FROM dbo.UnspecifiedTableName
)
DELETE x WHERE rn = 2;

As an aside, both [date] and [timestamp] are terrible choices for column names...

顺便说一句,[date]和[timestamp]都是列名的糟糕选择......

#2

If you don't mind the order of these rows there is a command in MySQL:

如果你不介意这些行的顺序,那么在MySQL中有一个命令:

DELETE TOP (numberOfRowsToDelete) FROM db.tablename WHERE {condition for ex id = 5};

#3

Do you have a primary key on the table?

桌上有主键吗?

What makes a row a duplicate? Same time? same date? all columns being the same?

是什么让一行重复?同时?同一天?所有列都一样吗?

If you have a primary key you can use the TOP function to select only one record and delete that one row:

如果您有主键,则可以使用TOP功能仅选择一个记录并删除该行:

Delete from [tablename] where id in (select top 1 id from [tablename] where [clause])

#4

For SQL Server 2005+ you can do the following:

对于SQL Server 2005+,您可以执行以下操作:

;WITH CTE AS
(
    SELECT  *, 
            ROW_NUMBER() OVER(PARTITION BY [date], calling, called, duration, [timestamp] ORDER BY 1) RN
    FROM YourTable
)
DELETE FROM CTE
WHERE RN = 2

#5

Since I don't have the schema, I'd a possible solution in steps:

由于我没有架构,我可以采取一些可能的解决方案:

Apply a row number to the select of all columns

将行号应用于所有列的选择

Make a group by with those columns and delete the min(rownumber) in each group

使用这些列创建一个组并删除每个组中的min(rownumber)

Edit:

The rownumber is in a inner query and will have the rownumber incrementing in all rows. In the outer query I make the group by of the inner query and select the min(rownumber) for each group. Since each group is composed by duplicated rows, I then remove the min(rownumber) for each group.

rownumber位于内部查询中,并且rownumber将在所有行中递增。在外部查询中,我通过内部查询创建组,并为每个组选择min(rownumber)。由于每个组由重复的行组成,因此我删除每个组的min(rownumber)。

#6

using LIMIT 1 will help you delete only 1 ROW that matches your DELETE query:

使用LIMIT 1将帮助您只删除与DELETE查询匹配的1个ROW:

DELETE FROM `table_name` WHERE `column_name`='value' LIMIT 1;

BEFORE:

+----------------------+
| id  |  column_name   |
+-----+----------------+
| 1   |  value         |
+-----+----------------+
| 2   |  value         |
+-----+----------------+
| 3   |  value         |
+-----+----------------+
| 4   |  value         |
+-----+----------------+

AFTER:

+----------------------+
| id  |  column_name   |
+-----+----------------+
| 1   |  value         |
+-----+----------------+
| 2   |  value         |
+-----+----------------+
| 3   |  value         |
+-----+----------------+

#1