奈寻味导航网 » 文章资讯 » MySQL SELECT DISTINCT和计数？

MySQL SELECT DISTINCT和计数？

2024-04-26 08:07:04 344

您需要将GROUPBY命令与MySQL的聚合函数count（*）结合使用才能实现。语法如下：

SELECT yourColumnName,COUNT(*) AS anyVariableNameFROM yourTableName GROUP BY yourColumnName;

为了理解上述语法，让我们创建一个表。创建表的查询如下：

mysql> create table selectDistinct_CountDemo
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> Name varchar(10),
   -> AppearanceId int,
   -> PRIMARY KEY(Id)
   -> );

使用insert命令在表中插入一些记录。查询如下：

mysql> insert into selectDistinct_CountDemo(Name,AppearanceId) values('Larry',1);
mysql> insert into selectDistinct_CountDemo(Name,AppearanceId) values('John',2);
mysql> insert into selectDistinct_CountDemo(Name,AppearanceId) values('Larry',3);
mysql> insert into selectDistinct_CountDemo(Name,AppearanceId) values('Larry',10);
mysql> insert into selectDistinct_CountDemo(Name,AppearanceId) values('Carol',11);
mysql> insert into selectDistinct_CountDemo(Name,AppearanceId) values('Larry',15);

使用select语句显示表中的所有记录。查询如下：

mysql> select *from selectDistinct_CountDemo;

以下是输出：

+----+-------+--------------+
| Id | Name  | AppearanceId |
+----+-------+--------------+
|  1 | Larry |            1 |
|  2 | John  |            2 |
|  3 | Larry |            3 |
|  4 | Larry |           10 |
|  5 | Carol |           11 |
|  6 | Larry |           15 |
+----+-------+--------------+
6 rows in set (0.00 sec)

这是选择不重复和计数的查询：

mysql> select Name,count(*) as TotalAppearance from selectDistinct_CountDemo
   -> group by Name;

以下是输出：

+-------+-----------------+
| Name  | TotalAppearance |
+-------+-----------------+
| Larry |               4 |
| John  |               1 |
| Carol |               1 |
+-------+-----------------+
3 rows in set (0.00 sec)

返回顶部
3162201930
czq8825@qq.com

MySQL SELECT DISTINCT和计数？

热门推荐

随机推荐