删除sql数据库中表中的重复行 [英] Remove duplicate Row in a Table in sql database

问题描述

嗨朋友们，

我使用sql server 2014版本来存储我网站的数据。现在我需要从sql表中删除重复的行。那么我怎么能这样做呢？

Hi friends,
I am using sql server 2014 version for storing data for my websites. Now I need to remove duplicate row from a sql table. So how can I do this?

推荐答案

最终，这些是我们要删除以解决问题的重复行。使用以下代码解决它。

Ultimately, these are the duplicate rows which we want to delete to resolve the issue. Use the following code to resolve it.

DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
	FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE) 

现在检查数据。表中没有重复的行。



是否过于复杂？

Now check the data. No duplicate rows exist in the table.

Is it too complicated?

解决问题的一般方法并防止它出现未来：



在表格中有一个标识字段*可以确保您永远不会有两个相同的行。拥有这样一个字段意味着您可以唯一地选择任何行，并且只对该行执行任何操作。问题将永远消失。



现在 - 在您当前的状态 - 一个简单的解决方案是在您的表中添加一个标识列 - 然后，找到您的行否则重复，并删除除了一个之外的所有字段，使用您希望的任何字段作为搜索条件来查找功能重复项。



更多预防医学：

您还可以根据需要在尽可能多的列上定义UNIQUE约束，并导致此类条目提前被拒绝。 SQL UNIQUE Con​​straint [ ^ ]  - 这会增加插入和更新的开销。





*默认值为GetDate（）的字段几乎一样好 - 但如果记录很快输入，它们可能具有相同的值。这是一个更大的数据字段，除非您需要日期，否则IDENTITY字段就是您的选择。

A general solutions to your problem and preventing it in the future:

Having an identity field* in your table is a way to assure that you can never have two identical rows. Having such a field means you can uniquely select any row and do whatever you wish to that only that row. Problem will be gone 'forever'.

Now - in your current state - an easy solution would be to add an identity column to your table - then, find your rows which are otherwise duplicates, and delete all but one using whatever fields you wish as the search criteria to find functional duplicates.

More Preventive Medicine:
You could also define a UNIQUE constraint on as many columns as necessary and cause such entries to be rejected in advance. SQL UNIQUE Constraint[^]   - this adds overhead to your inserts and updates.


* a field with a default value of GetDate() is almost as good - but if the records are entered very quickly they could potentially have the same value. It's a larger data field, and, unless you've a need for a date the IDENTITY field is the way to go.

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