有以下几表:
入库表
暂存表
发料表
退料表
退货表
报废表
上期未结存表
本期盘点库存=入库表+暂存表-发料表+退料表-退货表-报废表+上期未结存表
SQL语句怎么写?我这样写的但结果不正确
declare @sys_date datetime
set @sys_date=getdate()
insert into pd_table(St_ID,St_Name,yl_gg,Unit,sx,xx,kc_num) select stock.st_id, stock.sto_name,stock.spec spe,stock.unit,sum(isnull(stock.SX,0)) AS SX,sum(isnull(stock.XX,0)) AS XX,
sum(CaiGouRuKu_ZB.number)
+sum(isnull(SP_RuKu_zb.number,0))
-sum(isnull(Stuff_Open_zb.number,0))
-sum(isnull(Returns_zb.number,0))
+sum(isnull(Stuff_Out_zb.number,0))
-sum(isnull(Scrap_zb.number,0))
+sum(case when datepart(m,pd_table.pd_date) = datepart(m,@sys_date)-1 then pd_table.pd_num else 0 end)
as num
from stock --材料信息表
left join CaiGouRuKu_ZB
on stock.st_id=CaiGouRuKu_ZB.st_id and stock.sto_name=CaiGouRuKu_ZB.st_name and isnull(stock.spec,'')=CaiGouRuKu_ZB.yl_gg and stock.unit=CaiGouRuKu_ZB.unit
left join pd_table
on stock.st_id=pd_table.st_id and stock.sto_name=pd_table.st_name and isnull(stock.spec,'')=pd_table.yl_gg and stock.unit=pd_table.unit
left join SP_RuKu_zb
on stock.st_id=SP_RuKu_zb.st_id and stock.sto_name=SP_RuKu_zb.st_name and isnull(stock.spec,'')=SP_RuKu_zb.yl_gg and stock.unit=SP_RuKu_zb.unit
left join Stuff_Open_zb
on stock.st_id=Stuff_Open_zb.st_id and stock.sto_name=Stuff_Open_zb.st_name and isnull(stock.spec,'')=Stuff_Open_zb.yl_gg and stock.unit=Stuff_Open_zb.unit left join Returns_zb
on stock.st_id=Returns_zb.st_id and stock.sto_name=Returns_zb.st_name and isnull(stock.spec,'')=Returns_zb.yl_gg and stock.unit=Returns_zb.unit left join Stuff_Out_zb
on stock.st_id=Stuff_Out_zb.st_id and stock.sto_name=Stuff_Out_zb.st_name and isnull(stock.spec,'')=Stuff_Out_zb.yl_gg and stock.unit=Stuff_Out_zb.unit left join Scrap_zb
on stock.st_id=Scrap_zb.st_id and stock.sto_name=Scrap_zb.st_name and isnull(stock.spec,'')=Scrap_zb.yl_gg and stock.unit=Scrap_zb.unit
--where
group by stock.st_id, stock.sto_name,stock.spec,stock.unit
入库表
暂存表
发料表
退料表
退货表
报废表
上期未结存表
本期盘点库存=入库表+暂存表-发料表+退料表-退货表-报废表+上期未结存表
SQL语句怎么写?我这样写的但结果不正确
declare @sys_date datetime
set @sys_date=getdate()
insert into pd_table(St_ID,St_Name,yl_gg,Unit,sx,xx,kc_num) select stock.st_id, stock.sto_name,stock.spec spe,stock.unit,sum(isnull(stock.SX,0)) AS SX,sum(isnull(stock.XX,0)) AS XX,
sum(CaiGouRuKu_ZB.number)
+sum(isnull(SP_RuKu_zb.number,0))
-sum(isnull(Stuff_Open_zb.number,0))
-sum(isnull(Returns_zb.number,0))
+sum(isnull(Stuff_Out_zb.number,0))
-sum(isnull(Scrap_zb.number,0))
+sum(case when datepart(m,pd_table.pd_date) = datepart(m,@sys_date)-1 then pd_table.pd_num else 0 end)
as num
from stock --材料信息表
left join CaiGouRuKu_ZB
on stock.st_id=CaiGouRuKu_ZB.st_id and stock.sto_name=CaiGouRuKu_ZB.st_name and isnull(stock.spec,'')=CaiGouRuKu_ZB.yl_gg and stock.unit=CaiGouRuKu_ZB.unit
left join pd_table
on stock.st_id=pd_table.st_id and stock.sto_name=pd_table.st_name and isnull(stock.spec,'')=pd_table.yl_gg and stock.unit=pd_table.unit
left join SP_RuKu_zb
on stock.st_id=SP_RuKu_zb.st_id and stock.sto_name=SP_RuKu_zb.st_name and isnull(stock.spec,'')=SP_RuKu_zb.yl_gg and stock.unit=SP_RuKu_zb.unit
left join Stuff_Open_zb
on stock.st_id=Stuff_Open_zb.st_id and stock.sto_name=Stuff_Open_zb.st_name and isnull(stock.spec,'')=Stuff_Open_zb.yl_gg and stock.unit=Stuff_Open_zb.unit left join Returns_zb
on stock.st_id=Returns_zb.st_id and stock.sto_name=Returns_zb.st_name and isnull(stock.spec,'')=Returns_zb.yl_gg and stock.unit=Returns_zb.unit left join Stuff_Out_zb
on stock.st_id=Stuff_Out_zb.st_id and stock.sto_name=Stuff_Out_zb.st_name and isnull(stock.spec,'')=Stuff_Out_zb.yl_gg and stock.unit=Stuff_Out_zb.unit left join Scrap_zb
on stock.st_id=Scrap_zb.st_id and stock.sto_name=Scrap_zb.st_name and isnull(stock.spec,'')=Scrap_zb.yl_gg and stock.unit=Scrap_zb.unit
--where
group by stock.st_id, stock.sto_name,stock.spec,stock.unit
就是几张表的Jion
楼主要把业务逻辑搞清楚就好办了
set @sys_date=getdate()
insert into pd_table(St_ID,St_Name,yl_gg,Unit,sx,xx,kc_num) select stock.st_id, stock.sto_name,stock.spec spe,stock.unit,sum(isnull(stock.SX,0)) AS SX,sum(isnull(stock.XX,0)) AS XX,
sum(isnull(CaiGouRuKu_ZB.number,0))
+sum(isnull(SP_RuKu_zb.number,0))
-sum(isnull(Stuff_Open_zb.number,0))
-sum(isnull(Returns_zb.number,0))
+sum(isnull(Stuff_Out_zb.number,0))
-sum(isnull(Scrap_zb.number,0))
+sum(case when datepart(m,pd_table.pd_date) = datepart(m,@sys_date)-1 then isnull(pd_table.pd_num,0) else 0 end)
as num
from stock --材料信息表
left join CaiGouRuKu_ZB
on stock.st_id=CaiGouRuKu_ZB.st_id and stock.sto_name=CaiGouRuKu_ZB.st_name and isnull(stock.spec,'')=CaiGouRuKu_ZB.yl_gg and stock.unit=CaiGouRuKu_ZB.unit
left join pd_table
on stock.st_id=pd_table.st_id and stock.sto_name=pd_table.st_name and isnull(stock.spec,'')=pd_table.yl_gg and stock.unit=pd_table.unit
left join SP_RuKu_zb
on stock.st_id=SP_RuKu_zb.st_id and stock.sto_name=SP_RuKu_zb.st_name and isnull(stock.spec,'')=SP_RuKu_zb.yl_gg and stock.unit=SP_RuKu_zb.unit
left join Stuff_Open_zb
on stock.st_id=Stuff_Open_zb.st_id and stock.sto_name=Stuff_Open_zb.st_name and isnull(stock.spec,'')=Stuff_Open_zb.yl_gg and stock.unit=Stuff_Open_zb.unit left join Returns_zb
on stock.st_id=Returns_zb.st_id and stock.sto_name=Returns_zb.st_name and isnull(stock.spec,'')=Returns_zb.yl_gg and stock.unit=Returns_zb.unit left join Stuff_Out_zb
on stock.st_id=Stuff_Out_zb.st_id and stock.sto_name=Stuff_Out_zb.st_name and isnull(stock.spec,'')=Stuff_Out_zb.yl_gg and stock.unit=Stuff_Out_zb.unit left join Scrap_zb
on stock.st_id=Scrap_zb.st_id and stock.sto_name=Scrap_zb.st_name and isnull(stock.spec,'')=Scrap_zb.yl_gg and stock.unit=Scrap_zb.unit
--where
group by stock.st_id, stock.sto_name,stock.spec,stock.unit
试试再说
1, 10 (stockid=1 with opening quantity =10)
Receipt Table (StockID, QuantityRecevied) - assuming 2 records
1, 5
1, 10
Sales Table (stockID, quantitysold) - again, assuming 2 records
1, 2
1, 4Your query, reproduced in this simple setup, is something likeselect
a.stockid, a.openingstock+sum(isnull(b.quantityreceived, 0))-sum(isnull(c.quantitysold, 0))
from
stock a left join receipt b on
a.stockid=b.stockid
left join sales c on
a.stockid=c.stockid
group by
stockid, openingstockThe result is obviously wrong. If you remove the group by clause, the query actually returns 4 rows rather than 2 rows - stock linking to receipt produces 2 rows - further link to sales tables produces 4 rows.You should use subselect to achieve the closing quantity. Again using my vastly simplified example, the query would look something like the following:select
a.stockid,
a.openingstock +
coalesce((
select
sum(quantityreceived)
from
receipt
where
stockid=a.stockid
),0) -
coalesce((
select
sum(quantitysold)
from
sales
where
stockid=a.stockid
), 0)
from
stock a
材料信息表
材料编码 材料名称 材料规格 单位
001 WMS电阻 50R 个材料上期盘点表
材料编码 材料名称 材料规格 单位 上期存量 结存日期
001 WMS电阻 50R 个 0 2010-04-30材料入仓表
材料编码 材料名称 材料规格 单位 数量 日期
001 WMS电阻 50R 个 10 2010-05-4
001 WMS电阻 50R 个 15 2010-05-8
材料出仓表
材料编码 材料名称 材料规格 单位 数量 日期
001 WMS电阻 50R 个 20 2010-05-10求目前库存(上期盘点+入仓-出仓)
材料编码 材料名称 材料规格 单位 数量
001 WMS电阻 50R 个 5
但我现在求出的库存结果确是 -15,也就是有两条入算了两次出所以出现的错误结果
You effectively did 10+15-20-20 = -15I already explained what you did wrong and how to rectify the problem. 如果你看不懂英文,我也没办法了。