表AINQTY OUTQTY SQTY TOTAL
3000 200 100 2700
2000 500 100 (2700+2000-500-100)=4100
1000 200 200 (4100+1000-200-200)=4700如些类推
怎么样用SQL来计算TOTAL
3000 200 100 2700
2000 500 100 (2700+2000-500-100)=4100
1000 200 200 (4100+1000-200-200)=4700如些类推
怎么样用SQL来计算TOTAL
update A2.total = A1.total + A2.inqty+A2.Qutqty+A2.SQTY
set
from A A1,A A2
where A1.id = A2.id + 1
go
create table [tb] (INQTY int,OUTQTY int,SQTY int,TOTAL int)
insert into [tb]
select 3000,200,100,null union all
select 2000,500,100,null union all
select 1000,200,200,null
alter table tb
add id int identity
go
select INQTY,
OUTQTY,
SQTY,
(select sum(INQTY-OUTQTY-SQTY)from tb where id<=t.id)
from [tb] t
/*
INQTY OUTQTY SQTY
----------- ----------- ----------- -----------
3000 200 100 2700
2000 500 100 4100
1000 200 200 4700(3 個資料列受到影響)
*/
if object_id('[A]') is not null drop table [A]
go
create table [A]([INQTY] int,[OUTQTY] int,[SQTY] int,[TOTAL] int)
insert [A]
select 3000,200,100,2700 union all
select 2000,500,100,null union all
select 1000,200,200,null
---查询---
SELECT ID=IDENTITY(INT,1,1),* INTO # FROM A
GO
SELECT INQTY,OUTQTY,SQTY,
TOTAL=(SELECT SUM(INQTY)-SUM(OUTQTY)-SUM(SQTY) FROM # WHERE ID<=T.ID)
FROM # TDROP TABLE #---结果---
INQTY OUTQTY SQTY TOTAL
----------- ----------- ----------- -----------
3000 200 100 2700
2000 500 100 4100
1000 200 200 4700(3 行受影响)
INSERT @a SELECT 3000, 200, 100, NULL
union all select 2000, 500, 100, NULL
union all select 1000, 200, 200, NULL
DECLARE @s INT
SET @s=0
UPDATE @a SET @s=@s+INQTY-OUTQTY-SQTY ,TOTAL = @s SELECT * FROM @a
--result
/*INQTY OUTQTY SQTY TOTAL
----------- ----------- ----------- -----------
3000 200 100 2700
2000 500 100 4100
1000 200 200 4700(所影响的行数为 3 行)
*/
go
create table [tb] (INQTY int,OUTQTY int,SQTY int,TOTAL int)
insert into [tb]
select 3000,200,100,null union all
select 2000,500,100,null union all
select 1000,200,200,null
go
--2005
;with sql_sf
as
(select *,id=row_Number()over(order by (select 1))
from tb)
select INQTY,
OUTQTY,
SQTY,
(select sum(INQTY-OUTQTY-SQTY)from sql_sf where id<=t.id)TOTAL
from sql_sf t
/*
INQTY OUTQTY SQTY TOTAL
----------- ----------- ----------- -----------
3000 200 100 2700
2000 500 100 4100
1000 200 200 4700(3 個資料列受到影響)*/
3000 200 100 2700
2000 500 100 (2700+2000-500-100)=4100
1000 200 200 (4100+1000-200-200)=4700各位高手,忘记说了一点:就是INQTY中第一个数量(3000),其中2000是自己定的,里面只有1000的,是这样来的(自己定的2000+1000)
又怎么样写呢?
也就是说2000是上一次的结存,但上一次结存详细资料不用取,只给一个TOTAL数
所以说这个数是定的,我想通过以下方法
DECLARE @Firstqty as int
set @Firstqty=2000然后把2000加到第一条记录的INQTY里面,也就是2000+1000=3000
DECLARE @a table(INQTY INT, OUTQTY INT, SQTY INT, TOTAL INT)
INSERT @a SELECT 1000, 200, 100, NULL
union all select 2000, 500, 100, NULL
union all select 1000, 200, 200, NULL DECLARE @s INT ,@Init INT ,@i int
SELECT @i=0,@Init=2000,@s=@Init
UPDATE @a SET @i=@i+1, @s=@s+INQTY-OUTQTY-SQTY ,INQTY=CASE WHEN @i=1 THEN 2000+INQTY ELSE INQTY end,TOTAL = @s SELECT * FROM @a--result
/*INQTY OUTQTY SQTY TOTAL
----------- ----------- ----------- -----------
3000 200 100 2700
2000 500 100 4100
1000 200 200 4700(所影响的行数为 3 行)
*/