带有 case 语句的 Mysql SUM
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6878090/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql SUM with case statement
提问by Chris Muench
SELECT
SUM(
CASE
WHEN cumulative = 1
THEN percent
ELSE 0
END)
FROM phppos_items_taxes;
Given the above statement does this do the following:
鉴于上述声明，这样做会执行以下操作：
mysql> select * FROM phppos_items_taxes;
+---------+-----------+---------+------------+
| item_id | name | percent | cumulative |
+---------+-----------+---------+------------+
| 1 | Tax 1 | 8.00 | 0 |
| 1 | Tax 2 | 10.00 | 1 |
| 3 | Sales Tax | 8.00 | 0 |
| 4 | Tax 1 | 20.00 | 0 |
| 4 | Tax 2 | 20.00 | 0 |
+---------+-----------+---------+------------+
Does this SUM up percent for each row that cumulative = 1. If cumulative != 1 then 0 is summed.
是否对累积 = 1 的每一行进行此 SUM 增加百分比。如果累积 != 1，则求和为 0。
回答by Shef
Yes it does!
A shorter and cleaner query (IMHO) would be to use IFstatement:
是的，它确实！更短更清晰的查询（恕我直言）将使用IF语句：
SELECT SUM(IF(cumulative = 1, `percent`, 0)) FROM phppos_items_taxes;
回答by sll
Why not just to filter out items?
为什么不只是过滤掉项目？
SELECT
SUM(ISNULL(percent, 0))
FROM
phppos_items_taxes
WHERE
cumulative = 1
In your case each row will be selected and CASEstatement has to be applied, I believe with WHEREfilter it would be significantly faster because WHERE clause executing before SELECT clause
在您的情况下，将选择每一行并且CASE必须应用语句，我相信使用WHERE过滤器会更快，因为WHERE 子句在 SELECT 子句之前执行
