您可以为此使用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)