本文主要是介绍mysql优化查重,数据库：查重 去重，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
用户表全部数据如下，其中有人员的电话相同，mysql> select * from user;
+----+---------+-----------+-------------+-----+-------------------+---------+
| id | user_id | user_Name | tel | sex | addr | salary |
+----+---------+-----------+-------------+-----+-------------------+---------+
| 1 | 002 | 李四 | 13222222223 | 2 | haodeffkmgmgfflfl | 3500.34 |
| 2 | 001 | 张三 | 13222222223 | 2 | | 5500.45 |
| 3 | 003 | 王五 | 13222222222 | 1 | | 4567.32 |
+----+---------+-----------+-------------+-----+-------------------+---------+
3 rows in set (0.00 sec)
1、查询场景：查询被重复注册的电话，及被注册的次数， 注意 group by 的字段必须再查询结果的字段中mysql> select tel,count(*) from user group by tel;
+-------------+----------+
| tel | count(*) |
+-------------+----------+
| 13222222222 | 1 |
| 13222222223 | 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|
+-------------+-------+
| 13222222223 | 2 |
+-------------+-------+
1 row in set (0.00 sec)
3、查询场景：查询没个手机号的注册人的所有姓名，并用，分隔。
设计函数：group_concatmysql> select tel,group_concat(user_Name) userNames from user group by tel ;
+-------------+-----------+
| tel | userNames |
+-------------+-----------+
| 13222222222 | 王五 |
| 13222222223 | 李四,张三 |
+-------------+-----------+
2 rows in set (0.00 sec)
4、查询场景：查询用户表手机号，并去除重复
关键字：distinctmysql> select distinct tel from user;
+-------------+
| tel |
+-------------+
| 13222222223 |
| 13222222222 |
+-------------+
2 rows in set (0.00 sec)使用关键字 group by 实现去重mysql> select tel from user group by tel;
+-------------+
| tel |
+-------------+
| 13222222222 |
| 13222222223 |
+-------------+
2 rows in set (0.00 sec)
5、 查询场景：查询每个电话被注册最大id，及对应的电话mysql> select max(id),tel from user group by tel;
+---------+-------------+
| max(id) | tel |
+---------+-------------+
| 3 | 13222222222 |
| 2 | 13222222223 |
+---------+-------------+
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 where id in (select id from (select max(id) as 'id',tel from user group by tel) a);
+----+---------+-----------+-------------+-----+------+---------+
| id | user_id | user_Name | tel | sex | addr | salary |
+----+---------+-----------+-------------+-----+------+---------+
| 2 | 001 | 张三 | 13222222223 | 2 | | 5500.45 |
| 3 | 003 | 王五 | 13222222222 | 1 | | 4567.32 |
+----+---------+-----------+-------------+-----+------+---------+
2 rows in set (0.00 sec)
8、应用场景：删除重复记录，保留没个电话的最大id的那条记录mysql> delete from user where id not in (select id from (select max(id) as 'id',tel from user group by tel)a);
Query OK, 1 row affected (0.01 sec)
mysql> select * from user;
+----+---------+-----------+-------------+-----+------+---------+
| id | user_id | user_Name | tel | sex | addr | salary |
+----+---------+-----------+-------------+-----+------+---------+
| 2 | 001 | 张三 | 13222222223 | 2 | | 5500.45 |
| 3 | 003 | 王五 | 13222222222 | 1 | | 4567.32 |
+----+---------+-----------+-------------+-----+------+---------+
2 rows in set (0.00 sec)
mysql>
这篇关于mysql优化查重,数据库：查重 去重的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
