1. create table table1 (id int, ip varchar(15), city char(20))
2. insert table1
3. select 1, '127.0.0.1', '深圳' union
4. select 2, '127.0.0.2', '广州' union
5. select 3, '127.0.0.2', '广州' union
6. select 4, '127.0.0.2', '广州' union
7. select 5, '127.0.0.2', '北京' union
8. select 6, '127.0.0.4', '上海' union
9. select 7, '127.0.0.4', '上海' union
10. select 8, '127.0.0.4', '上海' union
11. select 9, '127.0.0.7', '未知'
12.
13. -- 按字段IP查询
14. select * from table1 where id in -- 检索重复ip的纪录
15. (select min(id) from table1 where ip in -- 求重复ip的其中一个id
16. (select ip from table1 group by ip having count(1) >= 1) -- 取出重复的ip
17. group by ip
18. )
19. drop table table1
20. 结果：
21. -- test again
22. create table table1 (id int, ip varchar(15), city char(20))
23. insert table1
24. select 1, '127.0.0.1', '深圳' union
25. select 2, '127.0.0.2', '广州' union
26. select 3, '127.0.0.2', '广州' union
27. select 4, '127.0.0.3', '北京' union
28. select 5, '127.0.0.3', '北京' union
29. select 6, '127.0.0.4', '上海' union
30. select 7, '127.0.0.4', '上海' union
31. select 8, '127.0.0.4', '上海' union
32. select 9, '127.0.0.7', '未知'
33.
34. -- 按字段city查询
35. select * from table1 where id in -- 检索重复city的纪录
36. (select min(id) from table1 where city in -- 求重复city的其中一个id
37. (select city from table1 group by city having count(1) >= 1) -- 取出重复的city
38. group by city
39. )
40. drop table table1
41. 结果：
42. 注释：
43. 如果是多表查询的话可以把数据放到临时表里然后在从临时表里按照刚才的方法查询