当前位置：文江博客话题详情

删除表中的重复项

发布于 2024-10-23 13:01:53 字数 208 浏览 4 评论 0原文

可能的重复：
从 Oracle 中的表中删除重复行

如何删除表中的重复行

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

末骤雨初歇 2024-10-30 13:01:53

如果您不喜欢数据库中的重复值，您也许应该设置唯一索引。

我已经为您测试了一个复杂的查询，它删除了表测试中具有相同名称的所有重复项。 id 最小的一个不会被删除。

您必须创建这些派生表（SELECT * FROM test），因为通常禁止在更新表中进行选择。

好好享受。

DELETE FROM `test` WHERE `id` IN (
SELECT t1.`id`
FROM (

SELECT *
FROM `test`
) AS `t1`
LEFT JOIN (

SELECT *
FROM `test`
) AS `t2` ON t2.`name` = t1.`name`
AND t2.`id` < t1.`id`
GROUP BY t1.`id`
HAVING count( t2.`id` ) >0
)

If you do not like doublicate values in the database, you maybe should set an unique index.

I have tested a complicated query for you which removes all dublicates in the table test which have an equal name. The one with the lowest id will not be removed.

You have to create these derived tables (SELECT * FROM test) because usually it is forbidden to do a select in the update table.

enjoy it.

DELETE FROM `test` WHERE `id` IN (
SELECT t1.`id`
FROM (

SELECT *
FROM `test`
) AS `t1`
LEFT JOIN (

SELECT *
FROM `test`
) AS `t2` ON t2.`name` = t1.`name`
AND t2.`id` < t1.`id`
GROUP BY t1.`id`
HAVING count( t2.`id` ) >0
)

回复收藏 0 原文

情深已缘浅 2024-10-30 13:01:53

http://www.kodyaz.com/articles /delete-duplicate-records-rows-in-a-table.aspx

通过假设，tablesample(firstname,lastname,id)

从用户中删除
哪里 ID 在 (
选择用户.Id
来自用户
INNER JOIN 重复项_CTE
ON Users.FirstName = Duplicates_CTE.FirstName
AND Users.LastName = Duplicates_CTE.LastName
AND Users.Id <>重复项_CTE.Id
）