第1个回答 2014-09-24
--SQL2008
--1
SELECT A.*,(SELECT SUM(Cash_In-Cash_Out)
FROM (SELECT 1 AS ID, 1000 as Cash_In, 0 AS Cash_Out
UNION SELECT 2 AS ID,0 AS Case_IN, 200 AS Cash_Out
UNION SELECT 3 AS ID,1500 AS Case_IN, 0 AS Cash_Out
UNION SELECT 4 AS ID,0 AS Case_IN, 200 AS Cash_Out
UNION SELECT 5 AS ID,0 AS Case_IN, 200 AS Cash_Out
)B
WHERE B.ID <= A.ID) AS CashNow
FROM
(SELECT 1 AS ID, 1000 as Cash_In, 0 AS Cash_Out
UNION SELECT 2 AS ID,0 AS Case_IN, 200 AS Cash_Out
UNION SELECT 3 AS ID,1500 AS Case_IN, 0 AS Cash_Out
UNION SELECT 4 AS ID,0 AS Case_IN, 200 AS Cash_Out
UNION SELECT 5 AS ID,0 AS Case_IN, 200 AS Cash_Out
)A
ORDER BY ID
--2
CREATE TABLE #A ( ID int,C1 varchar(10))
INSERT INTO #A
--SELECT 1 ,'AB'
--SELECT 2 ,'AC'
--SELECT 0 ,'bc'
--SELECT 0 ,'cc'
--SELECT 0 ,'bc'
SELECT CASE ID WHEN 0 THEN (ROW_NUMBER()OVER (partition by id ORDER BY id )
+(SELECT MAX(ID) FROM #A) )
ELSE ID END AS ID_New
, C1
FROM #A
ORDER BY ID_New
第2个回答 2014-09-24
select id,收入,支出,sum(decode(收入,null,0,收入)-decode(支出,null,0,支出)) over(order by id) 余额 from tt;
create sequence s_test start with 3 increment by 1;
update test set 序号=s_test.nextval where 序号=0;