如何将进销存3个表合成一个汇总表
┌├ ┬ ┼ ─ ┐│ ┌└ ┬ ┴ ┤ ┘
存表
┌──────┬────┬────┬───┬──┬──┬──┐
│日期 │产品编号│产品名称│仓号 │数量│单价│金额│
├──────┼────┼────┼───┼──┼──┼──┤
│2002-12-30 │00001 │主板 │一号仓│1 │500 │500 │
├──────┼────┼────┼───┼──┼──┼──┤
│2002-12-30 │00001 │主板 │二号仓│2 │600 │1200│
├──────┼────┼────┼───┼──┼──┼──┤
│2002-12-30 │00002 │CPU │二号仓│3 │400 │1200│
├──────┼────┼────┼───┼──┼──┼──┤
│2002-12-30 │00003 │机箱 │一号仓│100 │280 │2800│
└──────┴────┴────┴───┴──┴──┴──┘
进表
┌──────┬────┬────┬───┬──┬──┬──┐
│日期 │产品编号│产品名称│仓号 │数量│单价│金额│
├──────┼────┼────┼───┼──┼──┼──┤
│2003-01-01 │00001 │主板 │一号仓│1 │500 │500 │
├──────┼────┼────┼───┼──┼──┼──┤
│2003-01-01 │00001 │主板 │二号仓│2 │600 │1200│
├──────┼────┼────┼───┼──┼──┼──┤
│2003-01-02 │00002 │CPU │二号仓│3 │400 │1200│
├──────┼────┼────┼───┼──┼──┼──┤
│2003-01-03 │00003 │机箱 │一号仓│100 │280 │2800│
└──────┴────┴────┴───┴──┴──┴──┘
销表
┌──────┬────┬────┬───┬──┬──┬──┐
│日期 │产品编号│产品名称│仓号 │数量│单价│金额│
├──────┼────┼────┼───┼──┼──┼──┤
│2002-12-30 │00001 │主板 │一号仓│1 │500 │500 │
├──────┼────┼────┼───┼──┼──┼──┤
│2002-12-30 │00001 │主板 │二号仓│2 │600 │1200│
├──────┼────┼────┼───┼──┼──┼──┤
│2002-12-30 │00002 │CPU │二号仓│3 │400 │1200│
├──────┼────┼────┼───┼──┼──┼──┤
│2002-12-30 │00003 │机箱 │一号仓│100 │280 │2800│
└──────┴────┴────┴───┴──┴──┴──┘
用SQL语句建立类似下表的查询(也就是做一个汇总表)，可以用仓号，日期来查询数据。例如：
仓号：一号仓 日期：2003-01-01 到 2003-01-30
┌────┬────┬────┬────┬────┬────┬────┬────┬────┬────┐
│产品编号│产品名称│期初数量│期初金额│进货数量│进货金额│销售数量│销售金额│结存数量│结存金额│
├────┼────┼────┼────┼────┼────┼────┼────┼────┼────┤
│00001 │主板 │1 │500 │1 │500 │1 │500 │0 │0 │
├────┼────┼────┼────┼────┼────┼────┼────┼────┼────┤
│00003 │机箱 │100 │2800 │100 │2800 │1 │500 │0 │0 │
└────┴────┴────┴────┴────┴────┴────┴────┴────┴────┘
[解决办法]
不是很全
declare @cun table (NowTime datetime,coding char(10),baseNum nvarchar(20),Num int,price int,allprice int)
insert @cun select '2002-12-30','00001','一号仓',1,500,500 union all
select '2002-12-30','00001','二号仓',2,500,500 union all
select '2002-12-30','00002','二号仓',2,500,500
--select * from @cun
declare @jin table (NowTime datetime,coding char(10),baseNum nvarchar(20),Num int,price int,allprice int)
insert @jin select '2004-01-01','00001','一号仓',1,500,500 union all
select '2004-01-01','00001','二号仓',2,500,500 union all
select '2004-01-01','00002','二号仓',2,500,500
--select * from @jin
declare @xiao table (NowTime datetime,coding char(10),baseNum nvarchar(20),Num int,price int,allprice int)
insert @xiao select '2002-12-30','00001','一号仓',1,500,500 union all
select '2002-12-30','00001','二号仓',2,500,500 union all
select '2002-12-30','00002','二号仓',2,500,500
--select * from @xiao
declare @maxtime datetime
declare @mintime datetime
select distinct a.coding as 产品编号,a.Num as 期初数量,a.allprice as 期初金额,b.Num as 进货数量,b.allprice as 进货金额,
c.Num as 销售数量,c.allprice as 销售金额, (a.Num+b.Num-c.Num) as 结存数量,(a.allprice+b.allprice-c.allprice) as 结存数量,a.baseNum as 仓号
from @cun a left join @jin b on a.baseNum = b. baseNum
left join @xiao c on b.baseNum = c.baseNum
where a.baseNum = '二号仓'
--and c.NowTime<@maxtime
and c.NowTime<'2004-1-1'
--and a.NowTime>@mintime
and a.Nowtime>'2001-1-1'
[解决办法]
借用charry0110 的测试数据
declare @cun table (NowTime datetime,coding char(10),baseNum nvarchar(20),Num int,price int,allprice int)
insert @cun select '2002-12-30', '00001', '一号仓',1,500,500 union all
select '2002-12-30', '00001', '二号仓',2,500,500 union all
select '2002-12-30', '00002', '二号仓',2,500,500
--select * from @cun
declare @jin table (NowTime datetime,coding char(10),baseNum nvarchar(20),Num int,price int,allprice int)
insert @jin select '2003-01-01', '00001', '一号仓 ',1,500,500 union all
select '2003-01-02', '00001', '二号仓',2,500,500 union all
select '2003-01-03', '00002', '二号仓',2,500,500
--select * from @jin
declare @xiao table (NowTime datetime,coding char(10),baseNum nvarchar(20),Num int,price int,allprice int)
insert @xiao select '2002-12-30', '00001', '一号仓',1,500,500 union all
select '2002-12-30', '00001', '二号仓',2,500,500 union all
select '2002-12-30', '00002', '二号仓',2,500,500
--select * from @xiao
declare @baseNum nvarchar(20)
declare @maxtime datetime
declare @mintime datetime
select distinct a.baseNum as 仓号 ,a.coding as 产品编号,a.Num as 期初数量,a.allprice as 期初金额,b.Num as 进货数量,b.allprice as 进货金额,
c.Num as 销售数量,c.allprice as 销售金额, (a.Num+b.Num-c.Num) as 结存数量,(a.allprice+b.allprice-c.allprice) as 结存数量
from @cun a left join @jin b on a.coding = b.coding and a.baseNum = b.baseNum
left join @xiao c on b.coding = c.coding and b.baseNum = c.baseNum
where
(a.baseNum =null or a.baseNum like '%'+@baseNum+'%' )
and (@maxtime =null or c.NowTime <@maxtime)
and (@mintime =null or a.NowTime >@mintime )
