SQL 所有数据只保留一条，其他重复数据删除

作者: Tscccn | 日期: 2022 年 04 月 13 日 | 围观: 1371次+ 发表评论 (0) 查看评论

爬的免费代理ip，去重

CREATE TABLE `iproxy` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `ip` varchar(15) DEFAULT NULL,
  `port` varchar(11) DEFAULT '',
  `protocol` varchar(5) DEFAULT 'http',
  `status` char(1) DEFAULT 'Y',
  `bySite` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15427 DEFAULT CHARSET=utf8;

查询所有数据（保留一条），其他重复数据

SELECT * from iproxy where (ip,port) in (select ip,port from iproxy group by ip,port having count(*) > 1) and id not in (select min(id) from iproxy group by ip,port having count(*)>1)

所有数据只保留一条，其他重复数据删除

DELETE from iproxy where (ip,port) in (select ip,port from iproxy group by ip,port having count(*) > 1) and id not in (select min(id) from iproxy group by ip,port having count(*)>1)

－　ＴＨＥ　ＥＮＤ　－