本文介绍了从表格贷方借方列中选择运行余额的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个SQL Server 2008表，我需要从中选择一个运行余额
I have a SQL Server 2008 table, and I need to select a running balance from it
TransDate Credit Debit Datasource
------------------------------------------
2014-01-01 5000 NULL 3
2014-01-07 NULL 2000 3
2014-01-11 5000 NULL 3
2014-02-03 6000 NULL 4
2014-02-06 NULL 4000 4
2014-02-11 3000 NULL 4
2014-02-21 NULL 1000 3
2014-02-28 2000 NULL 3
2014-03-01 5000 NULL 3
我尝试了相关查询
Select
t.TransDate,
t.Credit,
t.Debit,
(Select sum(coalesce(x.credit, 0) - coalesce(x.debit, 0))
From Transactions x
WHERE x.DataSource IN (3,4) AND (x.TransDate >= '2014/02/01' AND x.TransDate <= '2014/02/28' )
AND x.TransDate = t.TransDate) Balance
From
Transactions t
但是我得到Balance的所有空值.
but I get all nulls for the Balance.
预期输出为
TransDate Credit Debit Balance
------------------------------------------
2014-01-11 NULL NULL 8000 <- opening balance
2014-02-03 6000 NULL 14000
2014-02-06 NULL 4000 10000
2014-02-11 3000 NULL 13000
2014-02-21 NULL 1000 12000
2014-02-28 2000 NULL 15000 <- closing balance
推荐答案
您需要自连接表.
CREATE TABLE Test
(
TransDate DATE,
Credit INT,
Debit INT,
);
INSERT INTO Test VALUES
('2014-01-01', 5000, NULL),
('2014-01-07', NULL, 2000),
('2014-01-11', 5000, NULL),
('2014-02-03', 6000, NULL),
('2014-02-06', NULL, 4000),
('2014-02-11', 3000, NULL),
('2014-02-21', NULL, 1000),
('2014-02-28', 2000, NULL),
('2014-03-01', 5000, NULL)
WITH CTE AS
(
SELECT t2.TransDate,
t2.Credit,
t2.Debit,
SUM(COALESCE(t1.credit, 0) - COALESCE(t1.debit, 0)) AS Balance
FROM Test t1
INNER JOIN Test t2
ON t1.TransDate <= t2.TransDate
WHERE t1.DataSource IN (3,4)
GROUP BY t2.TransDate, t2.Credit, t2.Debit
)
SELECT *
FROM CTE
WHERE (TransDate >= '2014/01/11' AND TransDate <= '2014/02/28' )
输出
TransDate Credit Debit Balance
2014-01-11 5000 (null) 8000
2014-02-03 6000 (null) 14000
2014-02-06 (null) 4000 10000
2014-02-11 3000 (null) 13000
2014-02-21 (null) 1000 12000
2014-02-28 2000 (null) 14000
这篇关于从表格贷方借方列中选择运行余额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！