GROUP BY số hàng trả về bởi GROUP BY trong MySQL?

Dùng GROUP_CONCAT() cho việc này. Tạo bảng để hiểu khái niệm:

mysql> create table groupByDemo 
    -> ( 
    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    -> Name varchar(100) 
    -> ); 
Query OK, 0 rows affected (1.31 sec)

Chèn bản ghi:

mysql> insert into groupByDemo(Name) values('John'); 
Query OK, 1 row affected (0.19 sec) 
mysql> insert into groupByDemo(Name) values('Carol'); 
Query OK, 1 row affected (0.14 sec) 
mysql> insert into groupByDemo(Name) values('Carol'); 
Query OK, 1 row affected (0.10 sec) 
mysql> insert into groupByDemo(Name) values('Bob'); 
Query OK, 1 row affected (0.24 sec) 
mysql> insert into groupByDemo(Name) values('Bob'); 
Query OK, 1 row affected (0.23 sec) 
mysql> insert into groupByDemo(Name) values('Bob'); 
Query OK, 1 row affected (0.17 sec) 
mysql> insert into groupByDemo(Name) values('John'); 
Query OK, 1 row affected (0.16 sec) 
mysql> insert into groupByDemo(Name) values('John'); 
Query OK, 1 row affected (0.07 sec) 
mysql> insert into groupByDemo(Name) values('John'); 
Query OK, 1 row affected (0.11 sec) 
mysql> insert into groupByDemo(Name) values('Sam'); 
Query OK, 1 row affected (0.17 sec) 
mysql> insert into groupByDemo(Name) values('Carol'); 
Query OK, 1 row affected (0.19 sec)

Hiển thị bản ghi:

mysql> select * from groupByDemo;

Kết quả:

+----+-------+ 
| 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)

Câu truy vấn GROUP BY số hàng:

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;

Kết quả:

+---------+------------+ 
| Counter | AllName    | 
+---------+------------+ 
| 4       | John       | 
| 3       | Carol, Bob | 
| 1       | Sam        | 
+---------+------------+ 
3 rows in set (0.00 sec)