create table table1 (id int, ip varchar(15), city char(20));
insert into table1 values(1, '127.0.0.1', '深圳') ;
insert into table1 values(2, '127.0.0.2', '广州') ;
insert into table1 values(3, '127.0.0.2', '广州') ;
insert into table1 values(4, '127.0.0.2', '广州') ;
insert into table1 values(5, '127.0.0.2', '北京') ;
insert into table1 values(6, '127.0.0.4', '上海') ;
insert into table1 values(7, '127.0.0.4', '上海') ;
insert into table1 values(8, '127.0.0.4', '上海') ;
insert into table1 values(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 ) ;
返回结果:
11127.0.0.1 深圳
22127.0.0.2 广州
36127.0.0.4 上海
49127.0.0.7 未知
create table table2 (id int, ip varchar(15), city char(20));
insert into table2 values( 1, '127.0.0.1', '深圳') ;
insert into table2 values( 2, '127.0.0.2', '广州') ;
insert into table2 values( 3, '127.0.0.2', '广州') ;
insert into table2 values( 4, '127.0.0.3', '北京') ;
insert into table2 values( 5, '127.0.0.3', '北京') ;
insert into table2 values( 6, '127.0.0.4', '上海') ;
insert into table2 values( 7, '127.0.0.4', '上海') ;
insert into table2 values( 8, '127.0.0.4', '上海') ;
insert into table2 values( 9, '127.0.0.7', '未知');
-- 按字段city查询
select * from table1 where id in
-- 检索重复city的纪录
(select min(id) from table1 where city in
-- 求重复city的其中一个id
(select city from table1 group by city having count(1) >= 1)
-- 取出重复的city
group by city );
返回结果:
11127.0.0.1 深圳
22127.0.0.2 广州
34127.0.0.3 北京
46127.0.0.4 上海
59127.0.0.7 未知