CREATE TABLE #T1(id INT IDENTITY,transDate DATETIME,transType VARCHAR(20),transAmount NUMERIC(10,2))
INSERT INTO #T1(transDate,transType,transAmount)
SELECT '2011-1-1','存款',2000 UNION ALL
SELECT '2011-1-3','存款',1000 UNION ALL
SELECT '2011-1-4','取现',-300 UNION ALL
SELECT '2011-1-7','转账',-500 UNION ALL
SELECT '2011-1-9','存款',500
现在需要生成下面这样报表,如何实现,谢谢
日期 类型 金额 余额
2011-1-1 存款 2000 2000
2011-1-3 存款 1000 3000
2011-1-4 取现 -300 2700
2011-1-7 转账 -500 2200
2011-1-9 存款 500 2700
INSERT INTO #T1(transDate,transType,transAmount)
SELECT '2011-1-1','存款',2000 UNION ALL
SELECT '2011-1-3','存款',1000 UNION ALL
SELECT '2011-1-4','取现',-300 UNION ALL
SELECT '2011-1-7','转账',-500 UNION ALL
SELECT '2011-1-9','存款',500
现在需要生成下面这样报表,如何实现,谢谢
日期 类型 金额 余额
2011-1-1 存款 2000 2000
2011-1-3 存款 1000 3000
2011-1-4 取现 -300 2700
2011-1-7 转账 -500 2200
2011-1-9 存款 500 2700
INSERT INTO #T1(transDate,transType,transAmount)
SELECT '2011-1-1','存款',2000 UNION ALL
SELECT '2011-1-3','存款',1000 UNION ALL
SELECT '2011-1-4','取现',-300 UNION ALL
SELECT '2011-1-7','转账',-500 UNION ALL
SELECT '2011-1-9','存款',500
go
select *,(select sum(transamount) from #t1 where transdate<=a.transdate)余额 from #t1 a
go
drop table #t1
/*
id transDate transType transAmount 余额
----------- ----------------------- -------------------- --------------------------------------- ---------------------------------------
1 2011-01-01 00:00:00.000 存款 2000.00 2000.00
2 2011-01-03 00:00:00.000 存款 1000.00 3000.00
3 2011-01-04 00:00:00.000 取现 -300.00 2700.00
4 2011-01-07 00:00:00.000 转账 -500.00 2200.00
5 2011-01-09 00:00:00.000 存款 500.00 2700.00(5 行受影响)
*/
declare @t TABLE(
id INT IDENTITY
,transDate DATETIME
,transType VARCHAR(20)
,transAmount NUMERIC(10,2)
)
INSERT INTO @t(transDate,transType,transAmount)
SELECT '2011-1-1','存款',2000 UNION ALL
SELECT '2011-1-3','存款',1000 UNION ALL
SELECT '2011-1-4','取现',-300 UNION ALL
SELECT '2011-1-7','转账',-500 UNION ALL
SELECT '2011-1-9','存款',500
;
select t1.id,t1.transDate,t1.transType,t2.transAmount
from @t t1
outer apply
(
select sum(transamount) as transamount
from @t
where id<=t1.id
) t2/*结果
id transDate transType transAmount
----------- ----------------------- -------------------- -------------------
1 2011-01-01 00:00:00.000 存款 2000.00
2 2011-01-03 00:00:00.000 存款 3000.00
3 2011-01-04 00:00:00.000 取现 2700.00
4 2011-01-07 00:00:00.000 转账 2200.00
5 2011-01-09 00:00:00.000 存款 2700.00
*/