使用包含ENUM字段的MySQL表计算项?
您可以使用GROUP BY和聚合函数COUNT()。首先让我们创建一个表 –
mysql> create table DemoTable
(
Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Size ENUM('S','M','L','XL')
);
Query OK, 0 rows affected (0.69 sec)
使用insert命令在表中插入一些记录 –
mysql> insert into DemoTable(Size) values('L');
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable(Size) values('S');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable(Size) values('S');
Query OK, 1 row affected (0.30 sec)
mysql> insert into DemoTable(Size) values('M');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable(Size) values('XL');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable(Size) values('M');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable(Size) values('M');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable(Size) values('M');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable(Size) values('XL');
Query OK, 1 row affected (0.08 sec)
使用select语句显示表中的所有记录 –
mysql> select *from DemoTable;
这将产生以下输出 –
+----+------+
| Id | Size |
+----+------+
| 1 | L |
| 2 | S |
| 3 | S |
| 4 | M |
| 5 | XL |
| 6 | M |
| 7 | M |
| 8 | M |
| 9 | XL |
+----+------+
9 rows in set (0.00 sec)
下面是查询使用table字段计算表中项的查询 –
mysql> SELECT tbl.Size, COUNT(1) AS Frequency from DemoTable tbl group by tbl.Size;
这将产生以下输出 –
+------+-----------+
| Size | Frequency |
+------+-----------+
| L | 1 |
| S | 2 |
| M | 4 |
| XL | 2 |
+------+-----------+
4 rows in set (0.00 sec)
阅读更多:MySQL 教程