sql - 案件和何时陈述的问题

我对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

最佳答案

尝试使用以下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

关于sql - 案件和何时陈述的问题，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/34469697/