sql2008 语句
本帖最后由 wgy155 于 2013-08-01 17:15:32 编辑 求解 写成 sql2008 语句
表名 aaa
借方金额 daifan
贷方金额 jeifan
合计 要加上 上一行的合计金额
生成报表
借方金额 贷方金额 合计
900 500 400
1000 600 800
700 200 1300
求解
[解决办法]
--你的表应该有rowid字段，那么就不用row_number生成了
;WITH cte AS
(
SELECT rowid = ROW_NUMBER() OVER(ORDER BY GETDATE()), *
FROM tb
)
SELECT
借方金额,
贷方金额,
合计 = (SELECT SUM(借方金额-贷方金额) FROM cte B WHERE B.rowid <= A.rowid)
FROM cte A
WITH tt(A,B)
AS
(
SELECT 900,500
UNION ALL
SELECT 1000,600
UNION ALL
SELECT 700,200
UNION ALL
SELECT 800,300
UNION ALL
SELECT 1200,600
)
, Temp
AS
(SELECT ROW_NUMBER() OVER (ORDER BY GETDATE()) AS ID
,A,B
FROM tt)--表？？
SELECT T.A
,T.B
,(T.A-T.B)+ISNULL((SELECT SUM(A-B) FROM Temp WHERE ID<T.ID ),0) AS Total
FROM Temp AS T
/*
ABTotal
900500400
1000600800
7002001300
8003001800
12006002400
*/
