y_baseinfo（基本信息）
ypid pinming jinjia
1001 室内机 500
1002 室外机 600
1003 多联机 700
y_liushui(流水表)
ypid shuliang caozuo riqi
1001 10 入库 2011-01-01
1001 10 出库 2011-02-15
1001 50 入库 2011-05-20
1003 80 入库 2011-03-20
1003 15 出库 2012-01-23
1003 5 退库 2012-01-25
y_kucun(库存表)
ypid shuliang caozuo riqi
1001 10 入库 2011-01-01
1001 -10 出库 2011-02-15
1001 50 入库 2011-05-20
1003 80 入库 2011-03-20
1003 -15 出库 2012-01-23
1003 5 退库 2012-01-25
1003 3 盘点 2012-01-25
1001 -1 盘点 2012-01-27
1001 -1 盘点 2012-01-27
要统计 2011-05-20 到 2012-01-31 间的结存表（ypid相同，y_baseinfo要左连接，没有显示为0，其中上期结存是小于开始日期(即前一天)的库存数没有为0，金额都乘以jinjia，本期收入=期间段入库+期间段退库，本期发出即期间段出库，本期结存是截止结束日期的库存，统计的库存数量以y_kucun表为准），
如下所要的统计表：
ypid pinming 上期结存数量 上期结存金额 本期收入数量 本期收入金额 本期发出数量 本期发出金额 本期盘点数量
1001 室内机 0 0 50 50*500=2500 0 0 -2
1002 室外机 0 0 0 0 0 0 0
1003 多联机 80 80*700=56000 5 5*700=3500 -15 -15*700=-10500 3
接上放不下
本期盘点金额 本期结存数量 本期结存金额
-2*500=-1000 48 48*500=24000
0 0 0
3*700 =2100 73 73*700=51100
------解决方案--------------------
--仔细看了看,貌似你的y_liushui(流水表)没有用处.更改为如下:
- SQL code
create table y_baseinfo(ypid int,pinming varchar(10),jinjia int)insert into y_baseinfo values(1001 ,'室内机', 500)insert into y_baseinfo values(1002 ,'室外机', 600)insert into y_baseinfo values(1003 ,'多联机', 700)create table y_liushui(ypid int,shuliang int,caozuo varchar(10),riqi datetime)insert into y_liushui values(1001 ,10 ,'入库', '2011-01-01')insert into y_liushui values(1001 ,10 ,'出库', '2011-02-15')insert into y_liushui values(1001 ,50 ,'入库', '2011-05-20')insert into y_liushui values(1003 ,80 ,'入库', '2011-03-20')insert into y_liushui values(1003 ,15 ,'出库', '2012-01-23')insert into y_liushui values(1003 ,5 ,'退库', '2012-01-25')create table y_kucun(ypid int,shuliang int,caozuo varchar(10),riqi datetime)insert into y_kucun values(1001 ,10 ,'入库', '2011-01-01')insert into y_kucun values(1001 ,-10 ,'出库', '2011-02-15')insert into y_kucun values(1001 ,50 ,'入库', '2011-05-20')insert into y_kucun values(1003 ,80 ,'入库', '2011-03-20')insert into y_kucun values(1003 ,-15 ,'出库', '2012-01-23')insert into y_kucun values(1003 ,5 ,'退库', '2012-01-25')insert into y_kucun values(1003 ,3 ,'盘点', '2012-01-25')insert into y_kucun values(1001 ,-1 ,'盘点', '2012-01-27')insert into y_kucun values(1001 ,-1 ,'盘点', '2012-01-27')goselect m.ypid ,m.pinming, 上期结存数量 = isnull((select sum(shuliang) from y_kucun n where n.ypid = m.ypid and convert(varchar(10),n.riqi,120) < '2011-05-20'),0), 上期结存金额 = isnull((select sum(shuliang) from y_kucun n where n.ypid = m.ypid and convert(varchar(10),n.riqi,120) < '2011-05-20'),0)*m.jinjia, 本期收入数量 = isnull((select sum(shuliang) from y_kucun n where n.ypid = m.ypid and n.caozuo in ('入库','退库') and convert(varchar(10),n.riqi,120) between '2011-05-20' and '2012-01-31'),0), 本期收入金额 = isnull((select sum(shuliang) from y_kucun n where n.ypid = m.ypid and n.caozuo in ('入库','退库') and convert(varchar(10),n.riqi,120) between '2011-05-20' and '2012-01-31'),0)*m.jinjia, 本期发出数量 = isnull((select sum(shuliang) from y_kucun n where n.ypid = m.ypid and n.caozuo = '出库' and convert(varchar(10),n.riqi,120) between '2011-05-20' and '2012-01-31'),0), 本期发出金额 = isnull((select sum(shuliang) from y_kucun n where n.ypid = m.ypid and n.caozuo = '出库' and convert(varchar(10),n.riqi,120) between '2011-05-20' and '2012-01-31'),0)*m.jinjia, 本期盘点数量 = isnull((select sum(shuliang) from y_kucun n where n.ypid = m.ypid and n.caozuo = '盘点' and convert(varchar(10),n.riqi,120) between '2011-05-20' and '2012-01-31'),0), 本期盘点金额 = isnull((select sum(shuliang) from y_kucun n where n.ypid = m.ypid and n.caozuo = '盘点' and convert(varchar(10),n.riqi,120) between '2011-05-20' and '2012-01-31'),0)*m.jinjia, 本期结存数量 = isnull((select sum(shuliang) from y_kucun n where n.ypid = m.ypid and convert(varchar(10),n.riqi,120) <= '2012-01-31'),0), 本期结存金额 = isnull((select sum(shuliang) from y_kucun n where n.ypid = m.ypid and convert(varchar(10),n.riqi,120) <= '2012-01-31'),0)*m.jinjiafrom y_baseinfo mdrop table y_baseinfo,y_liushui,y_kucun/*ypid pinming 上期结存数量 上期结存金额 本期收入数量 本期收入金额 本期发出数量 本期发出金额 本期盘点数量 本期盘点金额 本期结存数量 本期结存金额 ----------- ---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 1001 室内机 0 0 50 25000 0 0 -2 -1000 48 240001002 室外机 0 0 0 0 0 0 0 0 0 01003 多联机 80 56000 5 3500 -15 -10500 3 2100 73 51100（所影响的行数为 3 行）*/