基础信息表(t1)
id varchar(3) name varchar(10)
01 冷钢
02 钢板
03 扁钢--------------------------------
入库表(t2)
mc varchar(20)名称 sl int 数量 rq 日期 datetime
冷板 20 2006-1-1
冷板 10 2006-1-1
冷板 30 2006-1-2
钢板 50 2006-1-1
扁钢 20 2006-1-2
-----------------------------------------------------------------
出库表(t3)
mc varchar(20)名称 sl int 数量 rq 日期 datetime
冷板 5 2006-1-1
冷板 3 2006-1-2
钢板 2 2006-1-1
钢板 1 2006-1-2
扁钢 4 2006-1-1
扁钢 3 2006-1-2
-------------------------------------------------------------------
/*注意:每种板材都有100的期初库存数*/生成的报表为: 名称 入库数量 出库数量 结余数量 日期
冷板 30 5 125 2006-1-1
钢板 50 2 148 2006-1-1
扁钢 0 4 96 2006-1-1
-------------------------------------------------------------------------
以上为2006-1-1的数据,2006-1-2的数据与此类似,是通过日期来查询每一天的库存情况.谢谢大家帮助!!!!
id varchar(3) name varchar(10)
01 冷钢
02 钢板
03 扁钢--------------------------------
入库表(t2)
mc varchar(20)名称 sl int 数量 rq 日期 datetime
冷板 20 2006-1-1
冷板 10 2006-1-1
冷板 30 2006-1-2
钢板 50 2006-1-1
扁钢 20 2006-1-2
-----------------------------------------------------------------
出库表(t3)
mc varchar(20)名称 sl int 数量 rq 日期 datetime
冷板 5 2006-1-1
冷板 3 2006-1-2
钢板 2 2006-1-1
钢板 1 2006-1-2
扁钢 4 2006-1-1
扁钢 3 2006-1-2
-------------------------------------------------------------------
/*注意:每种板材都有100的期初库存数*/生成的报表为: 名称 入库数量 出库数量 结余数量 日期
冷板 30 5 125 2006-1-1
钢板 50 2 148 2006-1-1
扁钢 0 4 96 2006-1-1
-------------------------------------------------------------------------
以上为2006-1-1的数据,2006-1-2的数据与此类似,是通过日期来查询每一天的库存情况.谢谢大家帮助!!!!
名称 = name
入库数量=isnull((select sum(sl) from t2 where mc=t1.name and rq<='2006-01-01'),0),
出库数量=isnull((select sum(sl) from t2 where mc=t1.name and rq<='2006-01-01'),0),
结余数量=100+isnull((select sum(sl) from t2 where mc=t1.name and rq<='2006-01-01'),0)
-isnull((select sum(sl) from t2 where mc=t1.name and rq<='2006-01-01'),0),
日期 ='2006-01-01'
from
t1
(select mc,sum(sl) as rsl,rq from t1 group by mc,rq) a full join
(select mc,sum(sl) as csl,rq from t2 group by mc,rq) b
on a.mc=b.mc and a.rq=b.rq
select
名称 = name,
入库数量=isnull((select sum(sl) from t2 where mc=t1.name and rq<='2006-01-01'),0),
出库数量=isnull((select sum(sl) from t2 where mc=t1.name and rq<='2006-01-01'),0),
结余数量=100+isnull((select sum(sl) from t2 where mc=t1.name and rq<='2006-01-01'),0)-isnull((select sum(sl) from t2 where mc=t1.name and rq<='2006-01-01'),0),
日期 ='2006-01-01'
from
t1子陌的方法正确
create table t1 ([id] varchar(03),[name] varchar(10))
insert into t1
select '01','LG'
union
select '02','GB'
union
select '03','BG'create table t2(mc varchar(10),sl int,rq datetime)
insert into t2
select 'LG',20,'2006-01-01'
union all
select 'LG',10,'2006-01-01'
union all
select 'LG',30,'2006-01-02'
union all
select 'GB',50,'2006-01-01'
union all
select 'BG',20,'2006-01-02'create table t3(mc varchar(10),sl int,rq datetime)
insert into t3
select 'LG',5,'2006-01-01'
union all
select 'LG',3,'2006-01-02'
union all
select 'GB',2,'2006-01-01'
union all
select 'GB',1,'2006-01-02'
union all
select 'BG',4,'2006-01-01'
union all
select 'BG',3,'2006-01-02'/**/
declare @date datetime
set @date='2006-01-01'select A.[name] as 名称 ,isnull( B.入库数量,0) 入库数量 , isnull(C.出库数量,0) 出库数量 ,(100+isnull(D.s1,0)-isnull(E.s1,0)) as 结余数量,@date 日期
from t1 A
left join (select mc,sum(case when rq=@date then sl else 0 end) as 入库数量 from t2 group by mc) B on A.[name]=B.mc
left join (select mc,sum(case when rq=@date then sl else 0 end) as 出库数量 from t3 group by mc) C on A.[name]=C.mc
left join (select mc,sum(sl) s1 from t2 where rq<=@date group by mc) D on A.[name]=D.mc
left join (select mc,sum(sl) s1 from t3 where rq<=@date group by mc) E on A.[name]=E.mc
/*The result:*/
名称 入库数量 出库数量 结余数量 日期
------------------------------------------------------------
LG 30 5 125 2006-01-01 00:00:00.000
GB 50 2 148 2006-01-01 00:00:00.000
BG 0 4 96 2006-01-01 00:00:00.000
/*set @date='2006-01-02'*/
名称 入库数量 出库数量 结余数量 日期
--------------------------------------------------------------------
LG 30 3 152 2006-01-02 00:00:00.000
GB 0 1 147 2006-01-02 00:00:00.000
BG 20 3 113 2006-01-02 00:00:00.000
select
名称 = name,
入库数量=isnull((select sum(sl) from t2 where mc=t1.name and rq<='2006-01-01'),0),
出库数量=isnull((select sum(sl) from t2 where mc=t1.name and rq<='2006-01-01'),0),
结余数量=100+isnull((select sum(sl) from t2 where mc=t1.name and rq<='2006-01-01'),0)-isnull((select sum(sl) from t2 where mc=t1.name and rq<='2006-01-01'),0),
日期 ='2006-01-01'
from
t1/*The result:*/
名称 入库数量 出库数量 结余数量 日期
---------- ----------- ----------- ----------- ----------
LG 30 30 100 2006-01-01
GB 50 50 100 2006-01-01
BG 0 0 100 2006-01-01
名称 = name
入库数量=isnull((select sum(sl) from t2 where mc=t1.name and rq<='2006-01-01'),0),
出库数量=isnull((select sum(sl) from t3 where mc=t1.name and rq<='2006-01-01'),0),
结余数量=100+isnull((select sum(sl) from t2 where mc=t1.name and rq<='2006-01-01'),0)
-isnull((select sum(sl) from t3 where mc=t1.name and rq<='2006-01-01'),0),
日期 ='2006-01-01'
from
t1
名称 = name,
入库数量=isnull((select sum(sl) from t2 where mc=t1.name and rq<='2006-01-01'),0),
出库数量=isnull((select sum(sl) from t3 where mc=t1.name and rq<='2006-01-01'),0),
结余数量=100+isnull((select sum(sl) from t2 where mc=t1.name and rq<='2006-01-01'),0)-isnull((select sum(sl) from t3 where mc=t1.name and rq<='2006-01-01'),0),
日期 ='2006-01-01'
from
t1
名称 入库数量 出库数量 结余数量 日期
---------- ----------- ----------- ----------- ----------
LG 60 8 152 2006-01-02
GB 50 3 147 2006-01-02
BG 20 7 113 2006-01-02個人覺得入庫數量和出庫數量不應該對過去都求和,只要當天的.
insert into t1
select '01','冷钢'
union
select '02','钢板'
union
select '03','扁钢'create table t2(mc varchar(10),sl int,rq datetime)
insert into t2
select '冷钢',20,'2006-01-01'
union all
select '冷钢',10,'2006-01-01'
union all
select '冷钢',30,'2006-01-02'
union all
select '钢板',50,'2006-01-01'
union all
select '扁钢',20,'2006-01-02'create table t3(mc varchar(10),sl int,rq datetime)
insert into t3
select '冷钢',5,'2006-01-01'
union all
select '冷钢',3,'2006-01-02'
union all
select '钢板',2,'2006-01-01'
union all
select '钢板',1,'2006-01-02'
union all
select '扁钢',4,'2006-01-01'
union all
select '扁钢',3,'2006-01-02'
declare @dtInputDate datetimeset @dtInputDate='2006-01-01'select
a.name as '名称',
isnull(insl,0) as '入库数量',
isnull(outsl,0) as '出库数量',
100+isnull(insl,0)-isnull(outsl,0) as '结余数量',
@dtInputDate as '日期'
from t1 a
left join
(select sum(sl) as insl,mc from t2 where rq=@dtInputDate group by mc) b on a.name=b.mc
left join
(select sum(sl) as outsl,mc from t3 where rq=@dtInputDate group by mc) c on a.name=c.mc
刚才没看完,其实libin_ftsafe(子陌红尘:当libin告别ftsafe的写法都已经解决了
谢谢大家,揭贴了