mysql查重去重,MySQL数据库查重,去重(BeginCode出品)

用户表全部数据如下，其中有人员的电话相同，

mysql> select * from user;

+----+---------+-----------+-------------+-----+-------------------+---------+

| id | user_id | user_Name | tel | sex | addr | salary |

+----+---------+-----------+-------------+-----+-------------------+---------+

| 1 | 002 | 李四 | 132******** | 2 | haodeffkmgmgfflfl | 3500.34 |

| 2 | 001 | 张三 | 132******** | 2 | | 5500.45 |

| 3 | 003 | 王五 | 132******** | 1 | | 4567.32 |

+----+---------+-----------+-------------+-----+-------------------+---------+

3 rows in set (0.00 sec)

1、查询场景：

查询被重复注册的电话，及被注册的次数， 注意 group by 的字段必须再查询结果的字段中

mysql> select tel,count(*) from user group by tel;

+-------------+----------+

| tel | count(*) |

+-------------+----------+

| 132******** | 1 |

| 132******** | 2 |

+-------------+----------+

2 rows in set (0.01 sec)

2、查询场景：

查询重复注册的手机号次数大于2 的 , 是次数的 别名

mysql> select tel,count(*) regnum from user group by tel having regnum>1;

+-------------+-------+

| tel | regnum|

+-------------+-------+

| 132******** | 2 |

+-------------+-------+

1 row in set (0.00 sec)

3、查询场景：

查询没个手机号的注册人的所有姓名，并用，分隔。

设计函数：group_concat

mysql> select tel,group_concat(user_Name) userNames from user group by tel ;

+-------------+-----------+

| tel | userNames |

+-------------+-----------+

| 132******** | 王五 |

| 132******** | 李四,张三 |

+-------------+-----------+

2 rows in set (0.00 sec)

4、查询场景：

查询用户表手机号，并去除重复

关键字：distinct

mysql> select distinct tel from user;

+-------------+

| tel |

+-------------+

| 132******** |

| 132******** |

+-------------+

2 rows in set (0.00 sec)

使用关键字 group by 实现去重

mysql> select tel from user group by tel;

+-------------+

| tel |

+-------------+

| 132******** |

| 132******** |

+-------------+

2 rows in set (0.00 sec)

5、 查询场景：

查询每个电话被注册最大id，及对应的电话

mysql> select max(id),tel from user group by tel;

+---------+-------------+

| max(id) | tel |

+---------+-------------+

| 3 | 132******** |

| 2 | 132******** |

+---------+-------------+

2 rows in set (0.00 sec)

6、查询场景：

查询每个电话被注册最大id，

a 为第一个查询结果集的虚拟表别名

mysql> select id from (select max(id) as 'id',tel from user group by tel) a;

+------+

| id |

+------+

| 3 |

| 2 |

+------+

2 rows in set (0.00 sec)

7、查询场景：

查询每个电话的最大id的记录，

mysql> select * from user