从表中删除重复记录 - SQL 查询
我只需要从表中删除重复行,就像表中有 3 个重复行一样,我的查询将从 3 个重复行中删除 2 行。
我怎样才能得到这个?请帮我。
I need to delete duplicate rows only from the table, like I have 3 duplicate rows in the table, my query will delete 2 rows from 3 duplicated rows.
How can I get this? Please help me.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(7)
请尝试以下查询,它一定会满足您的目标
,其中 test 是您的表名称
Please try the below query, it will definitely meet your objective
where test is your table name
这在 SQL Server 中有效,尽管它不是单个语句:
它也不需要任何额外假设(例如存在使每行唯一的另一列)。毕竟,桑塔努确实说过行是重复的,而不仅仅是一列。
然而,在我看来,正确的答案是获得真正的表结构。也就是说,向该表添加一个 IDENTITY 列,以便您可以使用单个 SQL 命令来完成您的工作。像这样:
然后删除就很简单了:
This works in SQL Server although it isn't a single statement:
It also doesn't require any extra assumptions (like the existance of another column that makes each row unique). After all, Santanu did say that the rows were duplicates and not just the one column.
However, the right answer, in my view, is to get a real table structure. That is, add an IDENTITY column to this table so that you can use a single SQL command to do your work. Like this:
Then the delete is trivial:
将从
Table
(在colDup
列)中删除除最旧的(即lowsetdate
)之外的所有重复行。Will delete every duplicate row from
Table
(on columncolDup
) except the oldest (i.e. lowsetdate
).编辑:
我的错,上面的查询不起作用。
假设表结构:
id
int auto_incrementnum
int # <-- 这是具有重复值的列以下查询将在 MySQL 中运行(我检查过):
该查询将删除
num
列中具有 2 个(不能超过或其他)重复值的行。编辑(再次):
我建议在
num
列上添加一个键。编辑(#3):
如果作者想要删除重复的行,以下内容应该适用于MySQL(它对我有用):
假设表结构是:
Edit:
My bad, the above query won't work.
Assuming table structure:
id
int auto_incrementnum
int # <-- this is the column with duplicated valuesThe following query would work in MySQL (i checked):
The query would delete the rows that have 2 (not more or else) duplicated values in the
num
column.Edit (again):
I suggest to add a key on the
num
column.Edit(#3):
In case that the author wanted to delete the duplicated rows, the following should work for MySQL (it worked for me):
While assuming table structure is:
如果您有要删除的行的 ID,那么...
If you have the id's of the rows you want to delete then...
我认为每个表都有唯一的标识符。
因此,如果它存在,那么您可以编写以下查询:
从 Table1 t1 中删除 Table1,其中 2 >=(从 Table1 中选择 count(id),其中 dupColumn = t1.dupColumn)并且
t1.id 不在(从 Table1 中选择 max (id),其中 dupColumn = t1.dupColumn)
OOps。看来只能使用第二个过滤器
从 Table1 t1 中删除 Table1,其中
t1.id 不在(从 Table1 中选择 max (id),其中 dupColumn = t1.dupColumn)
I think each table has unique identifier.
So if it exists then you can write following query:
Delete Table1 from Table1 t1 where 2 >= (select count(id) from Table1 where dupColumn = t1.dupColumn) and
t1.id not in (select max (id) from Table1 where dupColumn = t1.dupColumn)
OOps. It seems it is possible to use second filter only
Delete Table1 from Table1 t1 where
t1.id not in (select max (id) from Table1 where dupColumn = t1.dupColumn)