MySQL Aggregate Function查找出现次数？
要计算表中的出现次数，可以将聚合函数COUNT()与GROUPBY一起使用。语法如下-
SELECT yourColumnName,COUNT(*) as anyVariableName from yourTableName GROUP BY yourColumnName;
为了理解上述语法，让我们创建一个表。创建表的查询如下-
mysql> create table CountOccurrences -> ( -> CarId int not null auto_increment, -> CarName varchar(30), -> PRIMARY KEY(CarId) -> );
使用insert命令在表中插入一些记录。查询如下-
mysql> insert into CountOccurrences(CarName) values('Aston Martin');
mysql> insert into CountOccurrences(CarName) values('BMW');
mysql> insert into CountOccurrences(CarName) values('Aston Martin');
mysql> insert into CountOccurrences(CarName) values('Honda');
mysql> insert into CountOccurrences(CarName) values('BMW');
mysql> insert into CountOccurrences(CarName) values('Audi');
mysql> insert into CountOccurrences(CarName) values('Aston Martin');
mysql> insert into CountOccurrences(CarName) values('Bugatti');
mysql> insert into CountOccurrences(CarName) values('BMW');
mysql> insert into CountOccurrences(CarName) values('Honda');
mysql> insert into CountOccurrences(CarName) values('Audi');
mysql> insert into CountOccurrences(CarName) values('BMW');使用select语句显示表中的所有记录。查询如下-
mysql> select *from CountOccurrences;
以下是输出-
+-------+--------------+ | CarId | CarName | +-------+--------------+ | 1 | Aston Martin | | 2 | BMW | | 3 | Aston Martin | | 4 | Honda | | 5 | BMW | | 6 | Audi | | 7 | Aston Martin | | 8 | Bugatti | | 9 | BMW | | 10 | Honda | | 11 | Audi | | 12 | BMW | +-------+--------------+ 12 rows in set (0.00 sec)
以下是使用count()GROUPBY来计算一列中每个值的出现次数的查询-
mysql> select CarName,count(*) as TotalCount from CountOccurrences -> group by CarName;
以下是输出-
+--------------+------------+ | CarName | TotalCount | +--------------+------------+ | Aston Martin | 3 | | BMW | 4 | | Honda | 2 | | Audi | 2 | | Bugatti | 1 | +--------------+------------+ 5 rows in set (0.00 sec)