sql - UNPIVOT 使用 FULL OUTER JOIN 和 SUM 的查询

Question

我有一个帐户和收入表，我正在尝试合并它们，然后再取消它们。

以下是每个表中的值：

收入：

账户：

我已经总结了所有内容并使用以下 sql 语句计算了差异：

SELECT        SUM(Income_tbl.Income) AS Income, SUM(Accounts_tbl.Amount) AS Debt, SUM(Income_tbl.Income) - SUM(Accounts_tbl.Amount) AS difference
FROM            Accounts_tbl
FULL OUTER JOIN Income_tbl
ON Accounts_tbl.id = Income_tbl.id

这是结果：

我想要做的是通过添加一个包含收入、债务和差异字段的“类型”列和一个包含金额的“AMT”列来取消这一点。

所以是这样的：

谢谢大家！

标签： sqlsql-servertsql

Answer 1

我不知道 afull outer join对此是否正确，但这不是您的问题。但是，只需使用子查询：

SELECT v.*
FROM (SELECT SUM(i.Income) AS Income, 
             SUM(a.Amount) AS Debt, 
             SUM(i.Income) - SUM(a.Amount) AS difference
      FROM Accounts_tbl a FULL OUTER JOIN
           Income_tbl i
           ON a.id = i.id
     ) ai CROSS APPLY
     (VALUES ('Income', Income), ('Debt', Debt), ('Difference', difference)
     ) v(type, amt);

这样做可能效率更高：

with ai as (
      select 'income' as type, sum(income) as amt
      from income_tbl
      union all
      select 'debt', sum(amount) as amt
      from accounts_tbl
     )
select ai.*
from ai
union all
select 'difference',
       sum(case when type = 'income' then amt else - amt end)
from ai;