今天一个朋友问我，一个表有两个字段mac和ip，如何找出所有的mac相同而ip不同的记录？想了半天写出了下面的这个SQL语句。

mysql> select * from ip;
+-----+-----+
| mac | ip  |
+-----+-----+
| abc | 123 |
| def | 456 |
| ghi | 245 |
| abc | 678 |
| def | 864 |
| abc | 123 |
| ghi | 245 |
+-----+-----+
7 rows in set (0.00 sec)

mysql> SELECT  DISTINCT a.mac, a.ip
    -> FROM ip a, ip b
    -> WHERE a.mac = b.mac AND a.ip <> b.ip ORDER BY a.mac;
+-----+-----+
| mac | ip  |
+-----+-----+
| abc | 678 |
| abc | 123 |
| def | 864 |
| def | 456 |
+-----+-----+
4 rows in set (0.00 sec)