MySQL查询求和一列中3个不同的值，以显示结果集中每个值的总和？
为此，您可以使用CASE语句。让我们首先创建一个表-
mysql> create table DemoTable
(
ProductName varchar(100),
ProductRating ENUM('1','2','3')
);使用插入命令在表中插入一些记录-
mysql> insert into DemoTable values('Product-1',3);
mysql> insert into DemoTable values('Product-2',1);
mysql> insert into DemoTable values('Product-3',2);
mysql> insert into DemoTable values('Product-1',2);
mysql> insert into DemoTable values('Product-3',3);
mysql> insert into DemoTable values('Product-2',2);
mysql> insert into DemoTable values('Product-3',3);使用select语句显示表中的所有记录-
mysql> select *from DemoTable;
这将产生以下输出-
+-------------+---------------+ | ProductName | ProductRating | +-------------+---------------+ | Product-1 | 3 | | Product-2 | 1 | | Product-3 | 2 | | Product-1 | 2 | | Product-3 | 3 | | Product-2 | 2 | | Product-3 | 3 | +-------------+---------------+ 7 rows in set (0.00 sec)
这是一个查询，用于对一列中的3个不同值求和，以显示结果集中每个值的总计。我们根据产品评分添加-
mysql> select ProductName, sum( case when ProductRating=3 then 1 else 0 end ) as Product_3_Rating, sum( case when ProductRating=2 then 1 else 0 end ) as Product_2_Rating, sum( case when ProductRating=1 then 1 else 0 end ) as Product_1_Rating from DemoTable group by ProductName;
这将产生以下输出-
+-------------+------------------+------------------+------------------+ | ProductName | Product_3_Rating | Product_2_Rating | Product_1_Rating | +-------------+------------------+------------------+------------------+ | Product-1 | 1 | 1 | 0 | | Product-2 | 0 | 1 | 1 | | Product-3 | 2 | 1 | 0 | +-------------+------------------+------------------+------------------+ 3 rows in set (0.00 sec)