create table table1 (id int, ip varchar(15), city char(20))
insert table1
select 1, '127.0.0.1', '深圳' union
select 2, '127.0.0.2', '广州' union
select 3, '127.0.0.2', '广州' union
select 4, '127.0.0.2', '广州' union
select 5, '127.0.0.2', '北京' union
select 6, '127.0.0.4', '上海' union
select 7, '127.0.0.4', '上海' union
select 8, '127.0.0.4', '上海' union
select 9, '127.0.0.7', '未知'
-- 按字段IP查询
select * from table1 where id in -- 检索重复ip的纪录
(select min(id) from table1 where ip in -- 求重复ip的其中一个id
(select ip from table1 group by ip having count(1) >= 1) -- 取出重复的ip
group by ip
)
insert table1
select 1, '127.0.0.1', '深圳' union
select 2, '127.0.0.2', '广州' union
select 3, '127.0.0.2', '广州' union
select 4, '127.0.0.2', '广州' union
select 5, '127.0.0.2', '北京' union
select 6, '127.0.0.4', '上海' union
select 7, '127.0.0.4', '上海' union
select 8, '127.0.0.4', '上海' union
select 9, '127.0.0.7', '未知'
-- 按字段IP查询
select * from table1 where id in -- 检索重复ip的纪录
(select min(id) from table1 where ip in -- 求重复ip的其中一个id
(select ip from table1 group by ip having count(1) >= 1) -- 取出重复的ip
group by ip
)