带条件的 SQL 求和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4517681/
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
SQL sum with condition
提问by Grace
I currently have a large SQL statement which i add the following line to in order to get the total cash for each transaction ID (which are unique):
我目前有一个大型 SQL 语句，我将以下行添加到其中以获取每个交易 ID（这是唯一的）的总现金：
select sum(cash) from Table a where a.branch = p.branch
and a.transID = p.transID) TotalCash
and i now need to do the same but only total the cash values that have a valuedate within the last month, so i have something like this:
我现在需要做同样的事情，但只汇总上个月内具有价值日期的现金价值，所以我有这样的事情：
select sum(CASE ValueDate WHEN > @startMonthDate THEN cash ELSE NULL END)
from Table a where a.branch = p.branch and a.transID = p.transID) TotalMonthCash
Sorry that I dont have the whole statement, but it is really long and specific to the context of the stored procedure but was hoping someone would know what i mean?
抱歉，我没有完整的语句，但它确实很长并且特定于存储过程的上下文，但希望有人知道我的意思？
回答by Mark Byers
Try this instead:
试试这个：
SUM(CASE WHEN ValueDate > @startMonthDate THEN cash ELSE 0 END)
Explanation
解释
Your CASE expression has incorrect syntax. It seems you are confusing the simple CASE expression syntax with the searched CASE expression syntax. See the documentation for CASE:
您的 CASE 表达式的语法不正确。您似乎将简单的 CASE 表达式语法与搜索的 CASE 表达式语法混淆了。请参阅CASE的文档：
The CASE expression has two formats:
- The simple CASE expression compares an expression to a set of simple expressions to determine the result.
- The searched CASE expression evaluates a set of Boolean expressions to determine the result.
CASE 表达式有两种格式：
- 简单 CASE 表达式将一个表达式与一组简单表达式进行比较以确定结果。
- 搜索的 CASE 表达式评估一组布尔表达式以确定结果。
You want the searched CASE expression syntax:
您需要搜索的 CASE 表达式语法：
CASE
WHEN Boolean_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
As a side note, if performance is an issue you may find that this expression runs more quickly if you rewrite using a JOIN and GROUP BY instead of using a dependent subquery.
作为旁注，如果性能是一个问题，您可能会发现如果使用 JOIN 和 GROUP BY 而不是使用依赖子查询重写，则此表达式运行得更快。
回答by James Wiseman
Try moving ValueDate:
尝试移动ValueDate：
select sum(CASE
WHEN ValueDate > @startMonthDate THEN cash
ELSE 0
END)
from Table a
where a.branch = p.branch
and a.transID = p.transID
(reformatted for clarity)
（为清晰起见重新格式化）
You might also consider using '0' instead of NULL, as you are doing a sum. It works correctly both ways, but is maybe more indicitive of what your intentions are.
您也可以考虑使用“0”而不是 NULL，因为您正在求和。它以两种方式正常工作，但可能更能说明您的意图。
回答by Jonathan Cedrim De Souza
With condition HAVING you will eliminate data with cash not ultrapass 0 if you want, generating more efficiency in your query.
使用条件 HAVING，如果需要，您将使用现金而不是ultrapass 0 消除数据，从而提高查询效率。
SELECT SUM(cash) AS money FROM Table t1, Table2 t2 WHERE t1.branch = t2.branch
AND t1.transID = t2.transID
AND ValueDate > @startMonthDate HAVING money > 0;
