如何使用COUNT(*)返回单行而不是多行？

您需要使用COUNT(*)的GROUP BY，以便进行分组并显示数量，从而消除多个值。让我们首先创建一个表：

mysql> create table DemoTable (Value int);
Query OK, 0 rows affected (0.55 sec)

以下是使用insert命令向表中插入一些记录的查询：

mysql> insert into DemoTable values(10);
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable values(20);
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values(10);
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values(30);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values(10);
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable values(20);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values(40);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values(10);
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable values(20);
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values(20);
Query OK, 1 row affected (0.07 sec)
mysql> insert into DemoTable values(60);
Query OK, 1 row affected (0.25 sec)

以下是使用select命令从表中显示记录的查询：

mysql> select *from DemoTable;

这将产生以下输出：

+-------+
| Value |
+-------+
|    10 |
|    20 |
|    10 |
|    30 |
|    10 |
|    20 |
|    40 |
|    10 |
|    20 |
|    20 |
|    60 |
+-------+
11 rows in set (0.00 sec)

以下是返回相似记录的计数，即10、20、30、40、60的查询：

mysql> SELECT COUNT(*) FROM (SELECT *FROM DemoTable GROUP BY Value) AS tbl;

这将产生以下输出：

+----------+
| COUNT(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

阅读更多：MySQL 教程