使用MySQL计算多行并在不同列（和一行）中显示结果
让我们首先创建一个表-
mysql> create table DemoTable1452 -> ( -> FavouriteColor varchar(50) -> );
使用插入命令在表中插入一些记录-
mysql> insert into DemoTable1452 values('Red');
mysql> insert into DemoTable1452 values('Yellow');
mysql> insert into DemoTable1452 values('Yellow');
mysql> insert into DemoTable1452 values('Yellow');
mysql> insert into DemoTable1452 values('Blue');
mysql> insert into DemoTable1452 values('Blue');
mysql> insert into DemoTable1452 values('Red');使用select语句显示表中的所有记录-
mysql> select * from DemoTable1452;
这将产生以下输出-
+----------------+ | FavouriteColor | +----------------+ | Red | | Yellow | | Yellow | | Yellow | | Blue | | Blue | | Red | +----------------+ 7 rows in set (0.00 sec)
以下是对多行进行计数并在不同列中显示结果的查询-
mysql> select sum(FavouriteColor='Red') as NumberOfRedColor, -> sum(FavouriteColor='Yellow') as NumberOfYellowColor, -> sum(FavouriteColor='Blue') as NumberOfBlueColor -> from DemoTable1452;
这将产生以下输出-
+------------------+---------------------+-------------------+ | NumberOfRedColor | NumberOfYellowColor | NumberOfBlueColor | +------------------+---------------------+-------------------+ | 2 | 3 | 2 | +------------------+---------------------+-------------------+ 1 row in set (0.27 sec)