在sql server 2005中使用两列运行总计/余额查询 [英] Running total/balance query in sql server 2005 with two column

问题描述

我想要输出如下，我有这四列，请帮我用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

这篇关于在sql server 2005中使用两列运行总计/余额查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！