select ID,Color,Size,InQty,OutQty,InQty-OutQty as 'InQty-OutQty' from 表名
思路,2005+可以用CTE 递归的算出2000的可以必须WHILE遍历每行给你个SQL2005+的语法,你参考下;WITH TB AS( SELECT ROW_NUMBER()OVER(ORDER BY GETDATE()) RN,ID,Color,Size,InQty,OutQty FROM [数据表] ) ,CTE AS( SELECT RN,ID,Color,Size,InQty,OutQty,InQty-OutQty ResultQty FROM TB2 WHERE RN=1 UNION ALL SELECT T1.RN,T1.ID,T1.Color,T1.Size,T1.InQty,T1.OutQty,T2.ResultQty+T1.InQty-T1.OutQty FROM TB2 T1 JOIN CTE T2 ON T1.RN=T2.RN+1 ) SELECT * FROM CTE以上代码假设你的ID是123,则需要编号出一个顺序,然后递归 如果你的ID是1到8这样的顺序则不需要再这一步,直接递归就可以了
CREATE TABLE #temp (ID int,Color int,[SIZE] VARCHAR(50),InQty INT,OutQty INT)INSERT #temp SELECT 1,123,'红色 X 20',10,0 UNION ALL SELECT 2,123,'黑色 X 20',12,0 UNION ALL SELECT 3,123,'红色 X 20',14,0 UNION ALL SELECT 4,123,'黑色 X 20',15,0 UNION ALL SELECT 5,123,'红色 X 20',2,0 UNION ALL SELECT 6,123,'黑色 X 20',8,0 UNION ALL SELECT 7,123,'红色 X 20',22,0 UNION ALL SELECT 8,123,'红色 X 20',19,0SELECT a.*,(SELECT SUM(inqty-outqty) FROM #temp WHERE id<=a.id) endqty FROM #temp a
INSERT #temp SELECT 1,123,'红色 X 20',10,0 UNION ALL SELECT 2,123,'黑色 X 20',12,0 UNION ALL SELECT 3,123,'红色 X 20',14,0 UNION ALL SELECT 4,123,'黑色 X 20',15,0 UNION ALL SELECT 5,123,'红色 X 20',2,0 UNION ALL SELECT 6,123,'黑色 X 20',8,0 UNION ALL SELECT 7,123,'红色 X 20',22,0 UNION ALL SELECT 8,123,'红色 X 20',19,0go with m as ( select a.* , a.InQty as sumQty from #temp a where ID = 1 union all select a.* , a.InQty + m.InQty from m , #temp a where a.ID = m.ID + 1 ) select * from mID Color SIZE InQty OutQty sumQty ----------- ----------- -------------------- ----------- ----------- ----------- 1 123 红色 X 20 10 0 10 2 123 黑色 X 20 12 0 22 3 123 红色 X 20 14 0 26 4 123 黑色 X 20 15 0 29 5 123 红色 X 20 2 0 17 6 123 黑色 X 20 8 0 10 7 123 红色 X 20 22 0 30 8 123 红色 X 20 19 0 41(8 行受影响)
drop table #temp CREATE TABLE #temp (ID int,Color int,[SIZE] VARCHAR(20),InQty INT,OutQty INT)
INSERT #temp SELECT 1,123,'红色 X 20',10,0 UNION ALL SELECT 2,123,'黑色 X 20',12,0 UNION ALL SELECT 3,123,'红色 X 20',14,0 UNION ALL SELECT 4,123,'黑色 X 20',15,0 UNION ALL SELECT 5,123,'红色 X 20',2,0 UNION ALL SELECT 6,123,'黑色 X 20',8,0 UNION ALL SELECT 7,123,'红色 X 20',22,0 UNION ALL SELECT 8,123,'红色 X 20',19,0go -- 上个回复加错列了,换成这个。 with m as ( select a.* , a.InQty as sumQty from #temp a where ID = 1 union all select a.* , a.InQty + m.sumQty from m , #temp a where a.ID = m.ID + 1 ) select * from m (8 行受影响) ID Color SIZE InQty OutQty sumQty ----------- ----------- -------------------- ----------- ----------- ----------- 1 123 红色 X 20 10 0 10 2 123 黑色 X 20 12 0 22 3 123 红色 X 20 14 0 36 4 123 黑色 X 20 15 0 51 5 123 红色 X 20 2 0 53 6 123 黑色 X 20 8 0 61 7 123 红色 X 20 22 0 83 8 123 红色 X 20 19 0 102
select ID,Color,Size,InQty,OutQty,InQty-OutQty as 'InQty-OutQty'
from 表名
SELECT ROW_NUMBER()OVER(ORDER BY GETDATE()) RN,ID,Color,Size,InQty,OutQty FROM [数据表]
)
,CTE AS(
SELECT RN,ID,Color,Size,InQty,OutQty,InQty-OutQty ResultQty FROM TB2 WHERE RN=1
UNION ALL
SELECT T1.RN,T1.ID,T1.Color,T1.Size,T1.InQty,T1.OutQty,T2.ResultQty+T1.InQty-T1.OutQty FROM TB2 T1 JOIN CTE T2 ON T1.RN=T2.RN+1
)
SELECT * FROM CTE以上代码假设你的ID是123,则需要编号出一个顺序,然后递归
如果你的ID是1到8这样的顺序则不需要再这一步,直接递归就可以了
CREATE TABLE #temp (ID int,Color int,[SIZE] VARCHAR(50),InQty INT,OutQty INT)INSERT #temp
SELECT 1,123,'红色 X 20',10,0 UNION ALL
SELECT 2,123,'黑色 X 20',12,0 UNION ALL
SELECT 3,123,'红色 X 20',14,0 UNION ALL
SELECT 4,123,'黑色 X 20',15,0 UNION ALL
SELECT 5,123,'红色 X 20',2,0 UNION ALL
SELECT 6,123,'黑色 X 20',8,0 UNION ALL
SELECT 7,123,'红色 X 20',22,0 UNION ALL
SELECT 8,123,'红色 X 20',19,0SELECT a.*,(SELECT SUM(inqty-outqty) FROM #temp WHERE id<=a.id) endqty
FROM #temp a
CREATE TABLE #temp (ID int,Color int,[SIZE] VARCHAR(50),InQty INT,OutQty INT)
INSERT #temp
SELECT 1,123,'红色 X 20',10,0 UNION ALL
SELECT 2,123,'黑色 X 20',12,0 UNION ALL
SELECT 3,123,'红色 X 20',14,0 UNION ALL
SELECT 4,123,'黑色 X 20',15,0 UNION ALL
SELECT 5,123,'红色 X 20',2,0 UNION ALL
SELECT 6,123,'黑色 X 20',8,0 UNION ALL
SELECT 7,123,'红色 X 20',22,0 UNION ALL
SELECT 8,123,'红色 X 20',19,0go
with m as
(
select a.* , a.InQty as sumQty from #temp a where ID = 1
union all
select a.* , a.InQty + m.InQty from m , #temp a where a.ID = m.ID + 1
)
select * from mID Color SIZE InQty OutQty sumQty
----------- ----------- -------------------- ----------- ----------- -----------
1 123 红色 X 20 10 0 10
2 123 黑色 X 20 12 0 22
3 123 红色 X 20 14 0 26
4 123 黑色 X 20 15 0 29
5 123 红色 X 20 2 0 17
6 123 黑色 X 20 8 0 10
7 123 红色 X 20 22 0 30
8 123 红色 X 20 19 0 41(8 行受影响)
drop table #temp
CREATE TABLE #temp (ID int,Color int,[SIZE] VARCHAR(20),InQty INT,OutQty INT)
INSERT #temp
SELECT 1,123,'红色 X 20',10,0 UNION ALL
SELECT 2,123,'黑色 X 20',12,0 UNION ALL
SELECT 3,123,'红色 X 20',14,0 UNION ALL
SELECT 4,123,'黑色 X 20',15,0 UNION ALL
SELECT 5,123,'红色 X 20',2,0 UNION ALL
SELECT 6,123,'黑色 X 20',8,0 UNION ALL
SELECT 7,123,'红色 X 20',22,0 UNION ALL
SELECT 8,123,'红色 X 20',19,0go
-- 上个回复加错列了,换成这个。
with m as
(
select a.* , a.InQty as sumQty from #temp a where ID = 1
union all
select a.* , a.InQty + m.sumQty from m , #temp a where a.ID = m.ID + 1
)
select * from m
(8 行受影响)
ID Color SIZE InQty OutQty sumQty
----------- ----------- -------------------- ----------- ----------- -----------
1 123 红色 X 20 10 0 10
2 123 黑色 X 20 12 0 22
3 123 红色 X 20 14 0 36
4 123 黑色 X 20 15 0 51
5 123 红色 X 20 2 0 53
6 123 黑色 X 20 8 0 61
7 123 红色 X 20 22 0 83
8 123 红色 X 20 19 0 102