Mysql Sum 条件

Question

我正在尝试从下表中获取总费用，如果设置了 Include_Extra，请使用相应 Extra_Seat_Cost 列中的值。我是 mysql 的新手，所以非常感谢任何帮助!

+-----------+-----------------+---------------+
| ID | Cost | Extra_Seat_Cost | Include_Extra |
+-----------+-----------------+---------------+
|  3 |   20 |      15         |       1       |
|  4 |   10 |      5          |       0       |
+----+------+-----------------+---------------+

结果应该总 yield = $45

Answer 1

您可以将 SUM 与 CASE WHEN 一起使用:

SELECT SUM(Cost + CASE WHEN Include_Extra = 1   --if Include_Extra is bool delete = 1
                       THEN COALESCE(Extra_Seat_Cost,0) 
                       ELSE 0 END) AS total
FROM table_name;

SqlFiddleDemo

我添加了 COALESCE 以防 Extra_Seat_Cost 可以为空。 number + NULL 产生 NULL。

---

如果你有分组列使用:

SELECT group_column, SUM(Cost + CASE WHEN Include_Extra = 1 
                                     THEN COALESCE(Extra_Seat_Cost,0) 
                                     ELSE 0 END) AS total
FROM table_name;
GROUP BY group_column;