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. 如果是多表查询的话可以把数据放到临时表里然后在从临时表里按照刚才的方法查询