极客教程在MySQL中如何在单个查询中计算两个不同的列？
您可以使用CASE语句在单个查询中计算两个不同的列。为了理解这个概念，让我们首先创建一个表。创建表的查询如下所示。
mysql> create table CountDifferentDemo
- > (
- > ProductId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
- > ProductName varchar(20),
- > ProductColor varchar(20),
- > ProductDescription varchar(20)
- > );
Query OK, 0 rows affected (1.06 sec)
使用insert命令插入一些记录。
查询如下所示：
mysql> insert into CountDifferentDemo(ProductName,ProductColor,ProductDescription) values('Product-1','Red','Used');
Query OK, 1 row affected (0.46 sec)
mysql> insert into CountDifferentDemo(ProductName,ProductColor,ProductDescription) values('Product-1','Blue','Used');
Query OK, 1 row affected (0.17 sec)
mysql> insert into CountDifferentDemo(ProductName,ProductColor,ProductDescription) values('Product-2','Green','New');
Query OK, 1 row affected (0.12 sec)
mysql> insert into CountDifferentDemo(ProductName,ProductColor,ProductDescription) values('Product-2','Blue','New');
Query OK, 1 row affected (0.14 sec)
mysql> insert into CountDifferentDemo(ProductName,ProductColor,ProductDescription) values('Product-3','Green','New');
Query OK, 1 row affected (0.21 sec)
mysql> insert into CountDifferentDemo(ProductName,ProductColor,ProductDescription) values('Product-4','Blue','Used');
Query OK, 1 row affected (0.20 sec)
使用select语句从表中显示所有记录。
查询如下所示：
mysql> select * from CountDifferentDemo;
输出如下
+-----------+-------------+--------------+--------------------+
| ProductId | ProductName | ProductColor | ProductDescription |
+-----------+-------------+--------------+--------------------+
| 1 | Product-1 | Red | Used |
| 2 | Product-1 | Blue | Used |
| 3 | Product-2 | Green | New |
| 4 | Product-2 | Blue | New |
| 5 | Product-3 | Green | New |
| 6 | Product-4 | Blue | Used |
+-----------+-------------+--------------+--------------------+
6 rows in set (0.01 sec)
下面是在单个查询中计算两个不同列（即计算特定颜色“红色”和描述“新”的发生次数）的查询。
mysql> select ProductName,
- > SUM(CASE WHEN ProductColor = 'Red' THEN 1 ELSE 0 END) AS Color,
- > SUM(CASE WHEN ProductDescription = 'New' THEN 1 ELSE 0 END) AS Desciption
- > from CountDifferentDemo
- > group by ProductName;
输出如下
+-------------+-------+------------+
| ProductName | Color | Desciption |
+-------------+-------+------------+
| Product-1 | 1 | 0 |
| Product-2 | 0 | 2 |
| Product-3 | 0 | 1 |
| Product-4 | 0 | 0 |
+-------------+-------+------------+
4 rows in set (0.12 sec)
阅读更多：MySQL 教程