MySQL-mysql 删除(除了第一条或最新一条)所有重复数据方法

MySQL-mysql 删除(除了第一条或最新一条)所有重复数据方法

晚风撩人 发布于 2017-06-15 字数 388 浏览 1050 回复 3

考虑性能高效的情况下,如何根据site字段分别快速的保留第一条和最新一条外,删除所有重复的数据

SELECT * FROM websites;
+----+--------+
| id | site |
+----+--------+
| 1 | baidu |
| 2 | yahoo |
| 3 | msn |
| 4 | baidu |
| 5 | google |
| 6 | yahoo |
| 7 | baidu |
| 8 | google |
| 9 | msn |

如果你对这篇文章有疑问,欢迎到本站 社区 发帖提问或使用手Q扫描下方二维码加群参与讨论,获取更多帮助。

扫码加入群聊

发布评论

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

评论(3

清晨说ぺ晚安 2017-09-30 3 楼

delete from websites where id not in (select id from websites where id not in( select id from websites where id in ( select min(id) from user group by site )) and id <> (select max(id) from websites));
这样应该可以了

灵芸 2017-09-09 2 楼

我觉得是不是理解错楼主的意思了,应该是:

delete from websites where 
id not in
   (( select min(id) from websites group by site ) 
         union all 
   (select max(id) from websites group by site)) tmp;

考虑到 mysql 会把 not in 转为 not exists 而临时表 tmp 上是没有索引的. 用左连接来做可能会快一些,具体还得做实验. 先把sql写下来:

delete websites from websites left join 
   (( select min(id) id from websites group by site ) 
         union all 
   (select max(id) id from websites group by site)) tmp
on websites.id=tmp.id 
 where tmp.id is null;
灵芸 2017-08-02 1 楼

随便写了个,性能不一定最高效,希望大家继续盖楼。。。

SELECT * FROM websites WHERE id NOT IN (SELECT id FROM websites WHERE id = (SELECT MAX(id) FROM websites) OR id=(SELECT MIN(id) FROM websites) ) GROUP BY site
UNION
SELECT * FROM websites WHERE id = (SELECT MAX(id) FROM websites) OR id=(SELECT MIN(id) FROM websites)
ORDER BY id ASC