要计算多列的值,请使用CASE语句。让我们首先创建一个表::mysql> create table countValueMultipleColumnsDemo
-> (
-> Value1 int,
-> Value2 int,
-> Value3 int
-> );
以下是使用insert命令在表中插入一些记录的查询:mysql> insert into countValueMultipleColumnsDemo values(10,15,10);
mysql> insert into countValueMultipleColumnsDemo values(20,30,10);
mysql> insert into countValueMultipleColumnsDemo values(40,10,60);
以下是使用select命令显示表中记录的查询:mysql> select *from countValueMultipleColumnsDemo;
这将产生以下输出+--------+--------+--------+
| Value1 | Value2 | Value3 |
+--------+--------+--------+
| 10 | 15 | 10 |
| 20 | 30 | 10 |
| 40 | 10 | 60 |
+--------+--------+--------+
3 rows in set (0.00 sec)
以下是对多个列的值进行计数的查询:mysql> SELECT (SUM(CASE WHEN Value1 = 10 THEN 1 ELSE 0 END) +
-> SUM(CASE WHEN Value2 = 10 THEN 1 ELSE 0 END) +
-> SUM(CASE WHEN Value3 = 10 THEN 1 ELSE 0 END)) TOTAL_COUNT
-> from countValueMultipleColumnsDemo;
这将产生以下输出+-------------+
| TOTAL_COUNT |
+-------------+
| 4 |
+-------------+
1 row in set (0.00 sec)