MySQL 如何将不同记录的计数显示在同一行中

对此，您可以使用GROUP_CONCAT（）以及GROUP BY子句的COUNT（）。让我们首先创建一个表 –

mysql> create table DemoTable
  -> (
  -> CompanyId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  -> CompanyName varchar(20)
  -> );
Query OK, 0 rows affected (0.62 sec)

使用插入命令将一些记录插入表中 –

mysql> insert into DemoTable(CompanyName) values('Amazon');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable(CompanyName) values('Google');
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable(CompanyName) values('Google');
Query OK, 1 row affected (0.07 sec)
mysql> insert into DemoTable(CompanyName) values('Microsoft');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable(CompanyName) values('Amazon');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable(CompanyName) values('Amazon');
Query OK, 1 row affected (0.07 sec)

使用select语句显示表中的所有记录 –

mysql> select *from DemoTable;

这将产生以下输出 –

+-----------+-------------+
| CompanyId | CompanyName |
+-----------+-------------+
|         1 | Amazon      |
|         2 | Google      |
|         3 | Google      |
|         4 | Microsoft   |
|         5 | Amazon      |
|         6 | Amazon      |
+-----------+-------------+
6 rows in set (0.00 sec)

以下是在MySQL中将不同记录的计数显示在同一行中的查询 –

mysql> select CompanyName,group_concat(CompanyId),count(CompanyId) as count from DemoTable
  -> group by CompanyName
  -> having count > 1;

这将产生以下输出 –

+-------------+-------------------------+-------+
| CompanyName | group_concat(CompanyId) | count |
+-------------+-------------------------+-------+
| Amazon      | 1,5,6                   |     3 |
| Google      | 2,3                     |     2 |
+-------------+-------------------------+-------+
2 rows in set (0.00 sec)