您需要为此使用GROUP BY和COUNT(*)来对值进行分组并显示消除多个值的计数。让我们首先创建一个表:
mysql> create table DemoTable (Value int);
以下是使用insert命令在表中插入一些记录的查询:
mysql> insert into DemoTable values(10); mysql> insert into DemoTable values(20); mysql> insert into DemoTable values(10); mysql> insert into DemoTable values(30); mysql> insert into DemoTable values(10); mysql> insert into DemoTable values(20); mysql> insert into DemoTable values(40); mysql> insert into DemoTable values(10); mysql> insert into DemoTable values(20); mysql> insert into DemoTable values(20); mysql> insert into DemoTable values(60);
以下是使用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)