两个case when出来的值如何相加

Question

CASE
WHEN f.presaletimeactual IS NULL THEN
NULL ELSE e.yqycontractamount * b.discount / 10000 
END as jia1,
CASE
WHEN f.presaletimeactual IS NULL THEN
NULL ELSE e.rwqfloorprice * b.discount / 10000 
END as jia2,
CASE
WHEN f.presaletimeactual IS NULL THEN
NULL ELSE e.ytwsfloortotalprice * b.discount / 10000 
END as jia3,

Answer 1

你好，

由于你直接case when as 的列不是原表的列，所以无法直接相加，你可以使用with CTE创建一个临时的结果集，然后select这个表中的列，将case when as的列直接相加即可。关于CTE的用法：WITH common_table_expression (Transact-SQL)

以下是Northwind数据库做的测试，供你参考

1. 直接查询原表[Order Details] 以及结果

SELECT Quantity,
CASE
    WHEN Quantity > 30 THEN 40
    ELSE 0
END AS Quantity1,
CASE
    WHEN Quantity = 30 THEN 30
    ELSE 0
END AS Quantity2

FROM [Order Details]

2.用WIth cte 定义临时结果集，再查询相加的列，结果如下

with cte as
(SELECT Quantity,
CASE
    WHEN Quantity > 30 THEN 40
    ELSE 0
END AS Quantity1,
CASE
    WHEN Quantity = 30 THEN 30
    ELSE 0
END AS Quantity2

FROM [Order Details])

select Quantity1,Quantity2, (Quantity1+Quantity2) as "Total" 
from cte

希望对你有用

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

请问您的问题解决了吗？
如果您觉得我们的回复解决了该问题，请帮忙‘标记为答案'以帮助其他社区成员迅速找到有用的答复。
如果没有，请回复并告诉我们当前情况，以便提供进一步的帮助。

谢谢

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].