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

  • 问题

  • 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,
    2020年7月15日 7:47

全部回复

  • 你好,

    由于你直接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 MSDNFSF@microsoft.com.

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

    谢谢


    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 MSDNFSF@microsoft.com.

    2020年7月16日 9:23