SQL查询计算同一行上的两个数量

可能是一个非常简单的答案,但我是T-SQL的新手,所以可以提供一些帮助! 我需要一个第三列来解决TotInc - (减去)TotEx给我一个TotalDisposableIncome 这是我的SQL: - 这给了我同一行的总收入和总支出
SELECT
    SUM(CASE WHEN Type = '1' THEN Amount ELSE 0 END) as TotalInc,
    SUM(CASE WHEN Type = '2' THEN Amount ELSE 0 END) as TotEx
    FROM ClaimFinancials
谢谢!     
已邀请:
您可以使用公用表表达式(CTE):
WITH T1 AS
(
    SELECT
        SUM(CASE WHEN Type = '1' THEN Amount ELSE 0 END) as TotalInc,
        SUM(CASE WHEN Type = '2' THEN Amount ELSE 0 END) as TotEx
    FROM ClaimFinancials
)
SELECT TotalInc, TotEx, TotalInc - TotEx AS TotalDisposableIncome
FROM T1
或普通的子查询:
SELECT TotalInc, TotEx, TotalInc - TotEx AS TotalDisposableIncome
FROM
(
    SELECT
        SUM(CASE WHEN Type = '1' THEN Amount ELSE 0 END) as TotalInc,
        SUM(CASE WHEN Type = '2' THEN Amount ELSE 0 END) as TotEx
    FROM ClaimFinancials
) T1
    
您无法在SELECT子句中的其他位置引用列别名。这是另一种选择。
SELECT TotalInc, TotEx, TotInc - TotEx as TotalDisposable
FROM (
    SELECT
    SUM(CASE WHEN Type = '1' THEN Amount ELSE 0 END) as TotalInc,
    SUM(CASE WHEN Type = '2' THEN Amount ELSE 0 END) as TotEx
    FROM ClaimFinancials
    ) AS Total
    
有不止一种方法可以做到这一点。
WITH T1 AS 
( 
  SELECT 
    Type,    
    SUM(Amount as Total 
  FROM ClaimFinancials 
) 
SELECT 
  Inc.Total as TotalInc, 
  Ex.Total as TotEx, 
  Inc.Total - Ex.Total AS TotalDisposableIncome 
FROM T1 Inc, T2 Ex
where T1.Type = 1 and T2.Type = 2
    
SELECT
  SUM(CASE WHEN Type = '1' THEN Amount ELSE 0 END) as TotalInc, 
  SUM(CASE WHEN Type = '2' THEN Amount ELSE 0 END) as TotEx,
  SUM(CASE 
    WHEN Type = '1' THEN Amount 
    WHEN Type = '2' Then -Amount 
    ELSE 0
  END) AS TotalDisposableIncome
FROM ClaimFinancials
    

要回复问题请先登录注册