mysql distinct 和 group by

本文介绍了MySQL中基础的数据查询方法,包括简单的数据检索、去重查询、使用GROUP BY进行分组统计等常见操作。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

         刚好用到, 记录一下, 表为:

mysql> select * from t1;
+----+------+-----+---------------------+
| id | name | age | tt                  |
+----+------+-----+---------------------+
| 30 | n1   |  10 | 2017-10-19 10:28:23 |
| 31 | n1   |  10 | 2017-10-19 10:28:42 |
| 32 | n1   |  15 | 2017-10-19 10:28:53 |
| 33 | n2   |  15 | 2017-10-19 10:29:03 |
+----+------+-----+---------------------+
4 rows in set (0.00 sec)

       找name:

mysql> select name from t1;
+------+
| name |
+------+
| n1   |
| n1   |
| n1   |
| n2   |
+------+
4 rows in set (0.00 sec)
       

       去重找name:

mysql> select distinct name from t1;
+------+
| name |
+------+
| n1   |
| n2   |
+------+
2 rows in set (0.01 sec)
mysql> select count(distinct name) from t1;
+----------------------+
| count(distinct name) |
+----------------------+
|                    2 |
+----------------------+
1 row in set (0.01 sec)

        去重找(name, age)的组合:

mysql> select distinct name, age from t1;
+------+-----+
| name | age |
+------+-----+
| n1   |  10 |
| n1   |  15 |
| n2   |  15 |
+------+-----+
3 rows in set (0.01 sec)
mysql> select count(distinct name, age) from t1;
+---------------------------+
| count(distinct name, age) |
+---------------------------+
|                         3 |
+---------------------------+
1 row in set (0.00 sec)


       用上group by:

mysql> select * from t1 group by name;
+----+------+-----+---------------------+
| id | name | age | tt                  |
+----+------+-----+---------------------+
| 30 | n1   |  10 | 2017-10-19 10:28:23 |
| 33 | n2   |  15 | 2017-10-19 10:29:03 |
+----+------+-----+---------------------+
2 rows in set (0.00 sec)
mysql> select count(*) from t1 group by name;
+----------+
| count(*) |
+----------+
|        3 |
|        1 |
+----------+
2 rows in set (0.00 sec)
mysql> select name, count(*) from t1 group by name;
+------+----------+
| name | count(*) |
+------+----------+
| n1   |        3 |
| n2   |        1 |
+------+----------+
2 rows in set (0.00 sec)
mysql> select name, age, count(*) from t1 group by name, age;
+------+-----+----------+
| name | age | count(*) |
+------+-----+----------+
| n1   |  10 |        2 |
| n1   |  15 |        1 |
| n2   |  15 |        1 |
+------+-----+----------+
3 rows in set (0.01 sec)

        简单东西, 不多说。  可以把select name, count(*) from t1 group by name; 固定下来, 作为一个基本的“范式”应用。



        



评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值