我需要您帮助我从一个表中为金融交易及其行提取一个查询，如下所示：Accountnumber:例如（4568643）TransactionMount:例如（1000美元）种类的交易（借记或贷记）transactionfile（该数字包含链接到此交易的文件号）
我需要你的帮助来提取查询的净余额后，每个（Transactionfile）的总和所有（Transaction天）持有信用从（Kindof交易）和减去它从总和所有（Transaction天）持有借记其中（帐号）是（4568643）
select transactionfile, sum(case when kindoftransaction = 'debit' then -1 else 1 end * transactionamount)
from financial
where accountnumber = 4568643
group by transactionfile
这有用吗请注意，如果可以发布表定义，则会有所帮助
DECLARE @transactions TABLE
(
[AccountNumber] INT NOT NULL,
[TransactionAmount] DECIMAL(18, 10) NOT NULL,
[KindOfTransaction] CHAR(1) NOT NULL, -- this could be better served as a boolean / bit field
[TransactionFile] INT NOT NULL
)
-- dummy data
INSERT INTO @transactions VALUES(4568643, 100, 'C', 123)
INSERT INTO @transactions VALUES(4568643, 150, 'C', 124)
INSERT INTO @transactions VALUES(4568643, 50, 'D', 125)
INSERT INTO @transactions VALUES(2345623, 100, 'C', 126)
INSERT INTO @transactions VALUES(2345623, 79, 'C', 127)
-- here's the actual query to return your data using a common table expression
;WITH cteAccountSummary (AccountNumber, TotalCredits, TotalDebits) AS (
SELECT [AccountNumber],
SUM(
CASE [KindOfTransaction]
WHEN 'C' THEN [TransactionAmount]
ELSE 0
END
),
SUM(
CASE [KindOfTransaction]
WHEN 'D' THEN [TransactionAmount]
ELSE 0
END
)
FROM @transactions
GROUP BY [AccountNumber]
)
SELECT [AccountNumber], TotalCredits - TotalDebits AS 'NetFinancialPosition'
FROM cteAccountSummary
