在两个字段上使用分组依据并在MySQL中计数？
为了在两个字段上实现GROUPBY并计数，让我们创建一个表。以下是创建表的查询-
mysql> create table GroupByTwoFieldsDemo −> ( −> Id int, −> Name varchar(200) −> );
让我们在表中插入一些记录-
mysql> insert into GroupByTwoFieldsDemo values(1,'John'); mysql> insert into GroupByTwoFieldsDemo values(10,'Johnson'); mysql> insert into GroupByTwoFieldsDemo values(9,'Carol'); mysql> insert into GroupByTwoFieldsDemo values(6,'Sam'); mysql> insert into GroupByTwoFieldsDemo values(5,'David'); mysql> insert into GroupByTwoFieldsDemo values(10,'Johnson'); mysql> insert into GroupByTwoFieldsDemo values(6,'Sam');
在select语句的帮助下显示表中的所有记录。查询如下-
mysql> select *from GroupByTwoFieldsDemo;
以下是输出-
+------+---------+ | Id | Name | +------+---------+ | 1 | John | | 10 | Johnson | | 9 | Carol | | 6 | Sam | | 5 | David | | 10 | Johnson | | 6 | Sam | +------+---------+ 7 rows in set (0.00 sec)
以下是将两列分组并计数的语法-
select yourColumnName1,yourColumnName2,....N,count(yourColumnName1) from GroupByTwoFieldsDemo group by yourColumnName1 desc,yourColumName2;
应用上述语法按两列分组并显示重复值的数量-
mysql> select Id,Name,count(Id) from GroupByTwoFieldsDemo group by id desc,Name;
以下是输出-
+------+---------+-----------+ | Id | Name | count(Id) | +------+---------+-----------+ | 10 | Johnson | 2 | | 9 | Carol | 1 | | 6 | Sam | 2 | | 5 | David | 1 | | 1 | John | 1 | +------+---------+-----------+ 5 rows in set, 1 warning (0.00 sec)