declare @ table (部门 varchar(8), 入库 int , 出库 int, 日期 datetime)insert into @ values('001', 12 , 3 , '2002-12-23')
insert into @ values('001', 13 , 5 , '2002-12-25')
insert into @ values('002', 3 , 2 , '2002-12-20')
insert into @ values('003', 6 , 5 , '2003-2-24')
select * from @select sum(入库-出库) as 库存 from @
where datepart(m,日期)=汇总月份
group by 部门
insert into @ values('001', 13 , 5 , '2002-12-25')
insert into @ values('002', 3 , 2 , '2002-12-20')
insert into @ values('003', 6 , 5 , '2003-2-24')
select * from @select sum(入库-出库) as 库存 from @
where datepart(m,日期)=汇总月份
group by 部门
insert into @ values('001', 13 , 5 , '2002-12-25')
insert into @ values('002', 3 , 2 , '2002-12-20')
insert into @ values('003', 6 , 5 , '2003-2-24')
select * from @select 部门,12 月份,sum(入库-出库) as 库存 from @
where datepart(m,日期)=12
group by 部门
"所有入库数量-所有出库数量"
有区别吗?
R1 C1 Y1=R1-C1
R2 C2 Y2=Y1+R2-C2=R1-C1+R2-C2
...
到最后Yn=Y1+R1-C1+R2-C2+...+Rn-Cn
=sum(Rn)-sum(Cn)
不就是所有入库-所有出库???
group by 部门,datepart(m,日期)
order by 部门,datepart(m,日期)
商品名称 char(8),
进货时间 datetime NULL ,
进货数量 int NULL
) ON [PRIMARY]INSERT TEST VALUES('a','2002-11-01 00:00:00.0',11)
GO
INSERT TEST VALUES('a','2002-11-02 00:00:00.0',34)
GO
INSERT TEST VALUES('a','2002-11-03 00:00:00.0',27)
GO
INSERT TEST VALUES('a','2002-11-04 00:00:00.0',43)
GO
INSERT TEST VALUES('b','2002-11-01 00:00:00.0',1)
GO
INSERT TEST VALUES('b','2002-11-02 00:00:00.0',10)
GO
INSERT TEST VALUES('b','2002-11-03 00:00:00.0',21)
GO
INSERT TEST VALUES('b','2002-11-04 00:00:00.0',40)
GO select l.商品名称,l.进货时间,l.进货数量, sum(r.进货数量 ) as 总进货数量
from test l
join test r
on l.进货时间 >= r.进货时间 and l.商品名称=r.商品名称
group by l.商品名称,l.进货时间, l.进货数量
order by l.商品名称,l.进货时间
a 2002-11-01 00:00:00.000 11 11
a 2002-11-02 00:00:00.000 34 45
a 2002-11-03 00:00:00.000 27 72
a 2002-11-04 00:00:00.000 43 115
b 2002-11-01 00:00:00.000 1 1
b 2002-11-02 00:00:00.000 10 11
b 2002-11-03 00:00:00.000 21 32
b 2002-11-04 00:00:00.000 40 72
select 商品名称,sum(进货数量)
from test
group by 商品名称
a 115
b 72
商品名称 char(8),
进货时间 datetime NULL ,
进货数量 int NULL
) ON [PRIMARY]INSERT TEST VALUES('a','2002-11-01 00:00:00.0',11)
GO
INSERT TEST VALUES('a','2002-11-02 00:00:00.0',34)
GO
INSERT TEST VALUES('a','2002-11-03 00:00:00.0',27)
GO
INSERT TEST VALUES('a','2002-11-04 00:00:00.0',43)
GO
INSERT TEST VALUES('b','2002-11-01 00:00:00.0',1)
GO
INSERT TEST VALUES('b','2002-11-02 00:00:00.0',10)
GO
INSERT TEST VALUES('b','2002-11-03 00:00:00.0',21)
GO
INSERT TEST VALUES('b','2002-11-04 00:00:00.0',40)
GO select l.商品名称,l.进货时间,l.进货数量, sum(r.进货数量 ) as 总进货数量
from test l
join test r
on l.进货时间 >= r.进货时间 and l.商品名称=r.商品名称
group by l.商品名称,l.进货时间, l.进货数量
order by l.商品名称,l.进货时间
a 2002-11-01 00:00:00.000 11 11
a 2002-11-02 00:00:00.000 34 45
a 2002-11-03 00:00:00.000 27 72
a 2002-11-04 00:00:00.000 43 115
b 2002-11-01 00:00:00.000 1 1
b 2002-11-02 00:00:00.000 10 11
b 2002-11-03 00:00:00.000 21 32
b 2002-11-04 00:00:00.000 40 72
select 商品名称,sum(进货数量)
from test
group by 商品名称
a 115
b 72