您需要将GROUP BY命令与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)