mysql distinct 和 group by

最新推荐文章于 2025-05-26 11:01:58 发布

原创最新推荐文章于 2025-05-26 11:01:58 发布 · 7.1k 阅读

4 ·

CC 4.0 BY-SA版权

s4: 数据库专栏收录该内容

101 篇文章

订阅专栏

本文介绍了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; 固定下来，作为一个基本的“范式”应用。