使用MySQL计算多行并在不同列中显示结果(以及单行)
首先创建一个表格 –
mysql> create table DemoTable1452
-> (
-> FavouriteColor varchar(50)
-> );
Query OK, 0 rows affected (2.42 sec)
使用插入命令向表格中插入一些记录 –
mysql> insert into DemoTable1452 values('红色');
Query OK, 1 row affected (0.31 sec)
mysql> insert into DemoTable1452 values('黄色');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable1452 values('黄色');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable1452 values('黄色');
Query OK, 1 row affected (0.23 sec)
mysql> insert into DemoTable1452 values('蓝色');
Query OK, 1 row affected (0.42 sec)
mysql> insert into DemoTable1452 values('蓝色');
Query OK, 1 row affected (0.33 sec)
mysql> insert into DemoTable1452 values('红色');
Query OK, 1 row affected (0.29 sec)
使用select语句显示表格中的所有记录 –
mysql> select * from DemoTable1452;
这将产生以下输出 –
+----------------+
| FavouriteColor |
+----------------+
| 红色 |
| 黄色 |
| 黄色 |
| 黄色 |
| 蓝色 |
| 蓝色 |
| 红色 |
+----------------+
7 rows in set (0.00 sec)
以下是计算多个行并将结果显示在不同列中的查询 –
mysql> select sum(FavouriteColor='红色') as NumberOfRedColor,
-> sum(FavouriteColor='黄色') as NumberOfYellowColor,
-> sum(FavouriteColor='蓝色') as NumberOfBlueColor
-> from DemoTable1452;
这将产生以下输出 –
+------------------+---------------------+-------------------+
| NumberOfRedColor | NumberOfYellowColor | NumberOfBlueColor |
+------------------+---------------------+-------------------+
| 2 | 3 | 2 |
+------------------+---------------------+-------------------+
1 row in set (0.27 sec)
阅读更多:MySQL 教程