计算 MySQL 中有多少行具有相同的值?
mysqlmysqli database
使用函数 COUNT(*) 和 GROUP BY 计算有多少行具有相同的值。语法如下 −
SELECT yourColumName1, count(*) as anyVariableName from yourTableName GROUP BY yourColumName1;
为了理解上述语法，让我们首先创建一个表。创建表的查询如下 −
mysql> create table RowWithSameValue −> ( −> StudentId int, −> StudentName varchar(100), −> StudentMarks int −> ); Query OK, 0 rows affected (0.55 sec)
插入一些具有相同值的记录。这里，我们为多个学生添加了相同的分数作为示例。插入记录的查询如下 −
mysql> insert into RowWithSameValue values(100,'Carol',89); Query OK, 1 row affected (0.21 sec) mysql> insert into RowWithSameValue values(101,'Sam',89); Query OK, 1 row affected (0.15 sec) mysql> insert into RowWithSameValue values(102,'John',99); Query OK, 1 row affected (0.12 sec) mysql> insert into RowWithSameValue values(103,'Johnson',89); Query OK, 1 row affected (0.15 sec)
现在您可以显示我们上面插入的所有记录。显示所有记录的查询如下 −
mysql> select *from RowWithSameValue;
以下是输出 −
+-----------+-------------+--------------+ | StudentId | StudentName | StudentMarks | +-----------+-------------+--------------+ | 100 | Carol | 89 | | 101 | Sam | 89 | | 102 | John | 99 | | 103 | Johnson | 89 | +-----------+-------------+--------------+ 4 rows in set (0.00 sec)
实现我们在开头讨论的语法来计算具有相同值的行数 −
mysql> SELECT StudentMarks, count(*) as SameValue from RowWithSameValue GROUP BY StudentMarks;
以下是显示多个值计数的输出 −
+--------------+-----------+ | StudentMarks | SameValue | +--------------+-----------+ | 89 | 3 | | 99 | 1 | +--------------+-----------+ 2 rows in set (0.00 sec)
