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

作者: Tscccn | 日期: 2022 年 04 月 13 日 | 围观: 621次+ 发表评论 (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;

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)

1	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)

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)