论坛问题：数值统计问题（非游标）

问题：

链接：http://bbs.csdn.net/topics/390985021

解决：

CREATE TABLE 租赁表(
 房间代码	NVARCHAR(30),
 日期		DATE,
 合计		DECIMAL(18,2),
 退款金额	DECIMAL(18,2),
 余额		DECIMAL(18,2)
)

INSERT INTO 租赁表
SELECT 'XSDGC-01-01-0003','2015-02-05',32780,NULL,NULL
UNION ALL
SELECT 'XSDGC-01-01-0003','2015-02-06',0,100,-100
UNION ALL
SELECT 'XSDGC-01-01-0003','2015-02-06',0,200,-200


SELECT * FROM 租赁表


;WITH TAB AS(
SELECT ROW_NUMBER()over(PARTITION BY 房间代码 ORDER BY (SELECT 0)) AS ID
,房间代码,日期,合计,退款金额,余额 FROM 租赁表
)
SELECT DISTINCT A.ID,A.房间代码,A.日期
,MAX(A.合计)OVER() - SUM(ISNULL(B.退款金额,0))OVER(PARTITION BY A.ID) +ISNULL(A.退款金额,0) AS 合计
,ISNULL(A.退款金额,0) AS 退款金额
,MAX(A.合计)OVER() - SUM(ISNULL(B.退款金额,0))OVER(PARTITION BY A.ID) AS 余额
FROM TAB A LEFT JOIN TAB B ON A.ID > B.ID-1