用户表全部数据如下，其中有人员的电话相同，
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_concat
mysql> select tel,group_concat(user_Name) userNames from user group by tel ;
+-------------+-----------+
| tel | userNames |
+-------------+-----------+
| 13222222222 | 王五 |
| 13222222223 | 李四,张三 |
+-------------+-----------+
2 rows in set (0.00 sec)
4、查询场景：
查询用户表手机号，并去除重复
关键字：distinct
mysql> 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>
网友评论