MySQL 如何按字段值求和

为了根据字段值进行求和，请使用聚合函数SUM()和CASE语句。首先让我们创建一个表 –

mysql> create table DemoTable
  (
  Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  Price int,
  isValidCustomer boolean,
  FinalPrice int
  );
Query OK, 0 rows affected (0.23 sec)

使用insert命令在表中插入一些记录 –

mysql> insert into DemoTable(Price,isValidCustomer,FinalPrice) values(20,false,40);
Query OK, 1 row affected (0.09 sec)

mysql> insert into DemoTable(Price,isValidCustomer,FinalPrice) values(45,true,10);
Query OK, 1 row affected (0.16 sec)

mysql> insert into DemoTable(Price,isValidCustomer,FinalPrice) values(89,true,50);
Query OK, 1 row affected (0.09 sec)

mysql> insert into DemoTable(Price,isValidCustomer,FinalPrice) values(200,false,100);
Query OK, 1 row affected (0.06 sec)

使用select语句显示表中的所有记录 –

mysql> select *from DemoTable;

这将产生以下输出 –

+----+-------+-----------------+------------+
| Id | Price | isValidCustomer | FinalPrice |
+----+-------+-----------------+------------+
| 1  | 20    | 0               | 40         |
| 2  | 45    | 1               | 10         |
| 3  | 89    | 1               | 50         |
| 4  | 200   | 0               | 100        |
+----+-------+-----------------+------------+
4 rows in set (0.00 sec)

以下是在MySQL中基于字段值求和的查询。在这里，对于FALSE（0），将添加FinalPrice，而对于TRUE（1），将添加Price –

mysql> select sum(case when isValidCustomer=true then Price else FinalPrice end) as TotalPrice from DemoTable;

这将产生以下输出 –

+------------+
| TotalPrice |
+------------+
| 274        |
+------------+
1 row in set (0.00 sec)

阅读更多：MySQL 教程