Problem about distinct in mysql query statements

Mysql query statements are often used. Today, the following requirements occur when you maintain the database. mysql query statements are used to find records that are not repeated in the user table and use distinct, but they can only be used for one field, I tried it many times. What should I do?

The reason is that distinct is used to return the number of records that do not repeat, instead of using it to return all values that do not record duplication.
That is, distinct can only return its target field, but cannot return other fields.
For example:

 
 

  
  
SELECT   DISTINCT mac,ip from ip  
  
  
+------+------+  
  
  
| mac   | ip |  
  
  
+------+------+  
  
  
| abc   |   678 |   
  
  
| abc   |   123 |   
  
  
| def   |   456 |   
  
  
| abc   | 12 |   
  
  
+------+------+  
 
 

He still won't change! Because the preceding statement serves two fields, that is, the mac and ip addresses must be the same to be excluded.

Finally, there is no way to use group !!!!
View mysql manual! Connt (distinct name) can be implemented with group.
A count function is used to implement the functions I want.

 
 

  
  
select *,count(distinct mac) from ip group by mac;  
  
  
+------+------+---------------------+  
  
  
| mac   | ip | count(distinct mac) |  
  
  
+------+------+---------------------+  
  
  
| abc   |   678 |                1 |   
  
  
| def   |   456 |                1 |   
  
  
+------+------+---------------------+  
 
 

Basically implement my ideas!

So how to implement a table with two fields mac and ip, and how to find records with the same mac and different ip addresses?

 
 

  
  
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)  
  
  
 
 
 

How to Implement MySQL full-text Query

Optimization of MySQL query Paging

MySQL query results are sorted by a certain value

Use functions to query row numbers in MySQL

Non-empty question in MySQL Query