我试图用余额,结果和收入返回账目清单
Account Transaction ------- ----------- AccountID TransactionID BankName AccountID Locale Amount Status
这是我目前拥有的.有人可以解释我在哪里错了吗？
select
a.ACCOUNT_ID,a.BANK_NAME,a.LOCALE,a.STATUS,sum(t1.AMOUNT) as BALANCE,sum(t2.AMOUNT) as OUTCOME,sum(t3.AMOUNT) as INCOME
from ACCOUNT a
left join TRANSACTION t1 on t1.ACCOUNT_ID = a.ACCOUNT_ID
left join TRANSACTION t2 on t2.ACCOUNT_ID = a.ACCOUNT_ID and t2.AMOUNT < 0
left join TRANSACTION t3 on t3.ACCOUNT_ID = a.ACCOUNT_ID and t3.AMOUNT > 0
group by a.ACCOUNT_ID,a.[STATUS]
UPDATE
根据下面的注释修改了t2左连接语法.
解决方法
既然你没有告诉我们
what’s going wrong(也就是描述除了描述你所期望的行为之外你所得到的行为),很难说出哪里,但有几种可能性.尼尔指出一个.另一个原因是,由于您在交易表中加入了三次,因此您将交易与交易配对并获得重复.而是单次加入事务表,并更改如何总结Amount列.
Select
a.ACCOUNT_ID,sum(t.AMOUNT) as BALANCE,sum((t.AMOUNT < 0) * t.AMOUNT) As OUTGOING,sum((t.AMOUNT > 0) * t.AMOUNT) As INCOMING
From ACCOUNT a
Left Join TRANSACTION t On t.ACCOUNT_ID = a.ACCOUNT_ID
Group By a.ACCOUNT_ID,a.[STATUS]
您可以使用CASE表达式作为乘法的更可读的替代方法：
Select
a.ACCOUNT_ID,a.[STATUS],sum(t.AMOUNT) As BALANCE,sum(CASE WHEN t.AMOUNT < 0 THEN t.AMOUNT ELSE 0 end) As OUTCOME,sum(CASE WHEN t.AMOUNT > 0 THEN t.AMOUNT ELSE 0 end) As INCOME
From ACCOUNT a
Left Join [TRANSACTION] t On t.ACCOUNT_ID = a.ACCOUNT_ID
Group By a.ACCOUNT_ID,a.[STATUS]
