代码之家 › 专栏 › 技术社区 › user1777929

在SQL Server 2014中,如何从另一个和值中减去一个和值?

subtraction sql-server

user1777929 · 技术社区 · 9 年前

我尝试了不同的代码组合,试图得到我想要的。但我认为最简单、最愚蠢的代码是:

SELECT A.AccountNumber
      ,SUM(
      (SELECT SUM(FinTransAmt) 
           FROM FinTransMaster
           WHERE FinTransCode IN ('Code01', 'Code02', 'Code03', 'Code04')
       )
    -      -- Minus sign
      (SELECT SUM(FinTransAmt) 
           FROM FinTransMaster
       WHERE FinTransCode IN ('Code05', 'Code06', 'Code07', 'Code08')
       ) 
      ) AS Interest

FROM FinTransMaster ft
   , Account a 

WHERE ft.AcctID=a.AccountId 
  AND a.AccountNumber = '101010101'
  AND CAST (ft.Created as date) >= '2017-06-01'
  AND CAST (ft.Created as date) <= '2017-07-01'

GROUP BY A.AccountNumber

2 回复 | 直到 8 年前

John Cappelletti 9 年前

也许是另一种选择

SELECT A.AccountNumber
      ,Interest = IsNull(SUM( case when FinTransCode IN ('Code01', 'Code02', 'Code03', 'Code04')  then FinTransAmt end),0)
                - IsNull(SUM( case when FinTransCode IN ('Code05', 'Code06', 'Code07', 'Code08')  then FinTransAmt end),0)
FROM FinTransMaster ft
   , Account a 
WHERE ft.AcctID=a.AccountId 
  AND a.AccountNumber = '101010101'
  AND CAST (ft.Created as date) >= '2017-06-01'
  AND CAST (ft.Created as date) <= '2017-07-01'
GROUP BY A.AccountNumber

Kannan Kandasamy 9 年前

SELECT A.AccountNumber
    ,Sum(case when FinTransCode in ('Code05', 'Code06', 'Code07', 'Code08') then -FinTransAmt else FinTransAmt end) as Interest
FROM FinTransMaster ft
     Join Account a 
     On ft.AcctID=a.AccountId 
  AND a.AccountNumber = '101010101'
  AND CAST (ft.Created as date) >= '2017-06-01'
  AND CAST (ft.Created as date) <= '2017-07-01'
GROUP BY A.AccountNumber

推荐文章