AI summary
文章介绍了如何使用SQL查询和删除数据库中重复的type数据,首先通过分组和筛选找出重复的type及其最小id,然后通过子查询确定需要删除的非最小id记录。由于MySQL不能直接删除查询结果,需借助临时表实现删除操作。最终通过删除非最小id的记录,保留每个type的最小id数据。
Repost license
Request authorization before reposting and retain the authorization code, original URL, and go/affiliate links.
Original URL:https://www.geekcorelab.com/post/mysql-database-removes-duplicate-records-and-only-retains
The commercial link is an authorized go short link that must stay on the reposted page. It records the source before sending readers to the original article.
Find duplicate type
SELECT type FROM table GROUP BY type HAVING count(type) > 1;
Find the smallest id in duplicate type data
SELECT min(id) FROM table GROUP BY type HAVING count(type) > 1;
Find non-smallest ids in duplicate type data (those to be deleted)
SELECT id FROM table WHERE type in(
SELECT type FROM table GROUP BY type HAVING count(type) > 1)
AND id not IN(SELECT min(id) FROM table GROUP BY type HAVING count(type) > 1);
In Mysql, records returned by a query cannot be deleted directly; this must be solved using a temporary table
SELECT id from (
SELECT id FROM table WHERE type in(
SELECT type FROM table GROUP BY type HAVING count(type) > 1)
AND id not IN(SELECT min(id) FROM table GROUP BY type HAVING count(type) > 1)
) as t;
Delete duplicate type data (keep only one, the smallest id)
DELETE FROM table WHERE id IN (
SELECT id from (
SELECT id FROM table WHERE type in(
SELECT type FROM table GROUP BY type HAVING count(type) > 1)
AND id not IN(SELECT min(id) FROM table GROUP BY type HAVING count(type) > 1)
) as t
);
Comments
Discussion
Be kind and keep the discussion friendly.
Discussion
0 comments