GROUP BY MySQL中GROUP BY返回的行数?
您可以为此使用GROUP_CONCAT()。为了理解上述概念,让我们创建一个表。
创建表的查询如下
mysql> create table groupByDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(100) -> );
使用insert命令在表中插入一些记录。
查询如下
mysql> insert into groupByDemo(Name) values('John'); mysql> insert into groupByDemo(Name) values('Carol'); mysql> insert into groupByDemo(Name) values('Carol'); mysql> insert into groupByDemo(Name) values('Bob'); mysql> insert into groupByDemo(Name) values('Bob'); mysql> insert into groupByDemo(Name) values('Bob'); mysql> insert into groupByDemo(Name) values('John'); mysql> insert into groupByDemo(Name) values('John'); mysql> insert into groupByDemo(Name) values('John'); mysql> insert into groupByDemo(Name) values('Sam'); mysql> insert into groupByDemo(Name) values('Carol');
使用select语句显示表中的所有记录。
查询如下
mysql> select *from groupByDemo;
以下是输出
+----+-------+ | Id | Name | +----+-------+ | 1 | John | | 2 | Carol | | 3 | Carol | | 4 | Bob | | 5 | Bob | | 6 | Bob | | 7 | John | | 8 | John | | 9 | John | | 10 | Sam | | 11 | Carol | +----+-------+ 11 rows in set (0.00 sec)
这是按行数分组的查询
mysql> SELECT Counter, GROUP_CONCAT(Name SEPARATOR ', ') as AllName -> FROM (SELECT Name, COUNT(Name) as Counter -> FROM groupByDemo -> GROUP BY Name) tbl -> GROUP BY Counter -> ORDER BY Counter DESC;
以下是输出
+---------+------------+ | Counter | AllName | +---------+------------+ | 4 | John | | 3 | Carol, Bob | | 1 | Sam | +---------+------------+ 3 rows in set (0.00 sec)