sql - Case 和 When 语句中的问题

Question

我对 Case 和 when 语句有疑问。我有两个 transtypeid 的列表，例如 10 和 12。

我尝试获取 sale1 金额，就像 transtypeid 11 具有总金额一样!=0 意味着，我需要减去 transtypeid 10 的总金额

我尝试了很多，但没有任何效果。

我尝试过这些查询

select 
    CT.CustomerCode, C.CustomerName,
    sale1 = case 
               when (ct.TransTypeID = 11) and (sum(ct.OVAmount  - ct.OVDiscount) != 0) 
                 then sum(ct.OVAmount - ct.OVDiscount) - sum(ct.OVAmount - ct.OVDiscount) 
                 else 0 
            end,
    C.CountryCode, C.CityCode 
from 
    CustomerTransactions CT
inner join 
    Customers C ON CT.CustomerCode = C.CustomerCode
where 
    ct.TransDate >= '2015-01-01' 
    and ct.TransDate <= '2015-12-31' 
    and ct.TransTypeID in (10, 11)
group by 
    ct.CustomerCode, c.CustomerName, c.CountryCode, c.CityCode

Answer 1

尝试使用以下 SQL 代码计算 sale1:

CASE WHEN 
    SUM(CASE WHEN ct.TransTypeID = 11
        THEN ct.OVAmount - ct.OVDiscount
        ELSE 0 END) != 0
    THEN
        SUM(CASE WHEN ct.TransTypeID = 11
            THEN ct.OVAmount - ct.OVDiscount
            ELSE O END)
        - SUM(CASE WHEN ct.TransTypeID = 10
              THEN ct.OVAmount - ct.OVDiscount
              ELSE 0 END)
    ELSE 0 END