根据CR-DR事务类型计算SUM

Question

我有以下结果集

BranchCode | AccountNo | TransType | TransAmount
ABC123 |    001    |   CR      |    1000
ABC123 |    002    |   CR      |    5000
ABC123 |    001    |   CR      |    3000
ABC123 |    001    |   DR      |    2000
ABC567 |    001    |   CR      |    8000
ABC567 |    002    |   CR      |    7000
ABC123 |    002    |   DR      |    1000
ABC567 |    002    |   DR      |    9000

我想要像一样的输出

BranchCode | AccountNo | BalanceType | BalanceAmount_CR| BalanceAmount_DR
ABC123   |    001    |     CR      |   2000          |     0.00
ABC123   |    002    |     CR      |   4000          |     0.00
ABC567   |    001    |     CR      |   8000          |     0.00 
ABC567   |    002    |     DR      |   0.00          |     2000

我尝试了多种方法，包括PIVOT、LEFT JOIN USING SAME TABLE，但每次我的结果都不一样。

我尝试的方法之一

SELECT  
BranchCode 
,AccountNo
,(CASE 
WHEN TransType='CR' THEN (SUM(TransAmount)) END)  AS BalanceAmount_CR
,(CASE 
WHEN TransType='DR' THEN (SUM(TransAmount)) END)  AS BalanceAmount_DR

FROM TransTable 
GROUP BY  BranchCode,AccountNo,TransType

当我在组中添加TransType时，它会创建新行并计算总和，我只想为每种类型的事务创建一行

附言：这不是任何一种学校项目，我有一个复杂的表格结构，但简化了我的问题，很容易理解。

提前谢谢。

Answer 1

我自己解决了。如果有人想知道，只需发布答案。

SELECT  
BranchCode 
,AccountNo
,SUM(CASE 
WHEN TransType='CR' THEN (TransAmount) END)  AS BalanceAmount_CR
,SUM(CASE 
WHEN TransType='DR' THEN (TransAmount) END)  AS BalanceAmount_DR

FROM TransTable 
GROUP BY  BranchCode,AccountNo

我刚刚移动了SUM函数。

Answer 2

看起来您实际上并不想按TransType分组。你似乎在做的是计算一个总余额CR-DR，然后把正负分为不同的列

您可以使用派生表：

SELECT 
BranchCode 
,AccountNo
,CASE WHEN BalanceAmount < 0 THEN 'DR' ELSE 'CR' END AS BalanceType
,CASE WHEN BalanceAmount >= 0 THEN BalanceAmount ELSE 0 END AS BalanceAmount_CR
,CASE WHEN BalanceAmount < 0 THEN -BalanceAmount ELSE 0 END AS BalanceAmount_DR
FROM (
SELECT  
BranchCode 
,AccountNo
,SUM(CASE WHEN TransType='CR' THEN TransAmount END) -
SUM(CASE WHEN TransType='DR' THEN TransAmount END) AS BalanceAmount
FROM TransTable 
GROUP BY  BranchCode,AccountNo
) AS t

您也可以将其放入CTE中，或者在SELECT中使用一组CASE表达式。