转ITPUB:如何根据最新的库存推算前期销售库存.

求助一个sql的写法

date stock sale
------- ---------- ------------
11-5-5 30 5
11-5-4 30 6
11-5-3 30 10
11-5-2 30 8
11-5-1 30 20
只有5号的stock是对的
销售都是对的
要倒推stock 前一天stock=当天stock+当天sale
4号是35，3号41，2号51 。。。。
有什么好的写法呢

 

---------------------------------------Solution---------------------------------------------------------

WITH DATA AS (
SELECT DATE '2011-5-5' AS THE_DATE, 30 AS STOCK, 5 AS SALE FROM DUAL
UNION ALL SELECT DATE '2011-5-4' AS THE_DATE, 30 AS STOCK, 6 AS SALE FROM DUAL
UNION ALL SELECT DATE '2011-5-3' AS THE_DATE, 30 AS STOCK, 10 AS SALE FROM DUAL
UNION ALL SELECT DATE '2011-5-2' AS THE_DATE, 30 AS STOCK, 8 AS SALE FROM DUAL
UNION ALL SELECT DATE '2011-5-1' AS THE_DATE, 30 AS STOCK, 20 AS SALE FROM DUAL
)
SELECT THE_DATE,SUM(CASE WHEN RN=1 THEN STOCK ELSE 0 END+SALE) OVER(ORDER BY THE_DATE DESC)-SALE AS STOCK, SALE
FROM (SELECT DATA.*
,ROW_NUMBER() OVER(ORDER BY THE_DATE DESC) rn
FROM DATA
);