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)