在sql server 2005中使用两列运行总计/余额查询
问题描述：
我想要输出如下，我有这四列，请帮我用sql查询生成第五列。
I want the output like below, i am having this four column, please help me to generate fifth column with sql query.
31-Mar-13 Product-A 50000 0 50000
2-Apr-13 Product-A 0 2000 48000
4-Apr-13 Product-A 0 3000 45000
6-Apr-13 Product-A 0 2500 42500
9-Apr-13 Product-A 0 2500 40000
11-Apr-13 Product-A 0 3000 37000
15-Apr-13 Product-A 0 3000 34000
16-Apr-13 Product-A 0 2000 32000
18-Apr-13 Product-A 0 1000 31000
20-Apr-13 Product-A 0 2000 29000
答
查看本文&自定义
在SQL中计算简单运行总计服务器 [ ^ ]
Check this article & customize it
Calculating simple running totals in SQL Server[^]
看看例子：
Have a look at example:
DECLARE @tmp TABLE(ID INT IDENTITY(1,1), PaymentIn INT, PaymentOut INT)
INSERT INTO @tmp (PaymentIn, PaymentOut)
VALUES(50000, 0), (0, 2000),
(0, 3000), (0, 2500),
(0, 2500), (0, 3000),
(0, 3000), (0, 2000),
(0, 1000), (0, 2000)
SELECT t1.ID, t1.PaymentIn, t1.PaymentOut, (SELECT SUM(t2.Balance)
FROM
(
SELECT ID, PaymentIn, PaymentOut, PaymentIn AS Balance
FROM @tmp
UNION ALL
SELECT ID, PaymentIn, PaymentOut, PaymentOut *-1 AS Balance
FROM @tmp
) AS t2 WHERE t2.ID<=t1.ID) AS Total
FROM @tmp AS t1
结果：
Result:
ID Pa.In Pa.Out Total
1 50000 0 50000
2 0 2000 48000
3 0 3000 45000
4 0 2500 42500
5 0 2500 40000
6 0 3000 37000
7 0 3000 34000
8 0 2000 32000
9 0 1000 31000
10 0 2000 29000
你好试试这个查询。
Hello Try this query.
select s.col1 as Date,s.col2 as Product,s.col3 as Cr,s.col4 as Db,(select sum(col3)-sum(col4) from this_table t where t.Col1 < s.col1) from this_table s