我目前有一个大的SQL语句,我添加以下行,以获得每个事务ID的总现金(这是唯一的):
select sum(cash) from Table a where a.branch = p.branch and a.transID = p.transID) TotalCash我现在需要做同样的事情,但只能总计在上个月有价值的现金价值,所以我有这样的事情:
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对不起,我没有整个声明,但它是存储过程的上下文真的很长和具体,但希望有人会知道我的意思?
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):
select sum(cash) from Table a where a.branch = p.branch and a.transID = p.transID) TotalCashand 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) TotalMonthCashSorry 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?
最满意答案
改为:
SUM(CASE WHEN ValueDate > @startMonthDate THEN cash ELSE 0 END)说明
您的CASE表达式语法不正确。 看来,您将简单的CASE表达式语法与搜索到的CASE表达式语法混淆。 请参阅CASE文档 :
CASE表达式有两种格式:
简单的CASE表达式将表达式与一组简单表达式进行比较,以确定结果。 搜索的CASE表达式计算一组布尔表达式以确定结果。
您想要搜索到的CASE表达式语法:
CASE WHEN Boolean_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END作为附注,如果性能是一个问题,您可能会发现,如果使用JOIN和GROUP BY重写,而不是使用依赖子查询,则此表达式运行速度更快。
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:
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.
You want the searched CASE expression syntax:
CASE WHEN Boolean_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] ENDAs 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.
更多推荐
发布评论