计算MySQL中已知不同值的出现

为此，您可以使用聚合函数SUM()。让我们首先创建一个表-

mysql> create table DemoTable636 (
   StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,StudentFirstName varchar(100)
);

使用插入命令在表中插入一些记录-

mysql> insert into DemoTable636(StudentFirstName) values('John');
mysql> insert into DemoTable636(StudentFirstName) values('Robert');
mysql> insert into DemoTable636(StudentFirstName) values('Robert');
mysql> insert into DemoTable636(StudentFirstName) values('Sam');
mysql> insert into DemoTable636(StudentFirstName) values('Mike');
mysql> insert into DemoTable636(StudentFirstName) values('John');
mysql> insert into DemoTable636(StudentFirstName) values('Robert');

使用select语句显示表中的所有记录-

mysql> select *from DemoTable636;

这将产生以下输出-

+-----------+------------------+
| StudentId | StudentFirstName |
+-----------+------------------+
|         1 | John             |
|         2 | Robert           |
|         3 | Robert           |
|         4 | Sam              |
|         5 | Mike             |
|         6 | John             |
|         7 | Robert           |
+-----------+------------------+
7 rows in set (0.00 sec)

以下是对已知（或枚举）不同值的出现进行计数的查询-

mysql> select
   sum(StudentFirstName='John') AS JOHN_COUNT,
   sum(StudentFirstName='Robert') AS ROBERT_COUNT,
   sum(StudentFirstName='Sam') AS SAM_COUNT,
   sum(StudentFirstName='Mike') AS MIKE_COUNT
   from DemoTable636;

这将产生以下输出-

+------------+--------------+-----------+------------+
| JOHN_COUNT | ROBERT_COUNT | SAM_COUNT | MIKE_COUNT |
+------------+--------------+-----------+------------+
|          2 |            3 | 1         | 1          |
+------------+--------------+-----------+------------+
1 row in set (0.00 sec)