27,582
社区成员




select
t0.SkuId , t0.SkuNo , t0.ProductNo, t0.ProductName, t3.StockId, t4.StockName
---这2个花了21秒
,(select top 1 OldQty from dbo.Wms_StockLog
where SkuId=t0.SkuId and StockId=t3.StockId
order by Id ) as OpeningStock --库存原数量
,(select top 1 NewQty from dbo.Wms_StockLog
where skuId=t0.SkuId and StockId=t3.StockId
order by id desc ) as ClosingStock--库存新数量
---下面2个花了7秒
--AddedQty 必须入库添加的商品数量 》0 必须+的表示 入库 《0 必须-的 出库
,(select sum(AddedQty)from dbo.Wms_StockLog where skuId=t0.SkuId and StockId=t3.StockId
and AddedQty>0) as PeriodIn --入库数量
,(select sum(AddedQty)from dbo.Wms_StockLog where skuId=t0.SkuId and StockId=t3.StockId
and AddedQty<0 )as PeriodOut--出库数量
from dbo.Prod_Sku AS t0 inner join
dbo.Wms_LocStock AS t2 on t0.SkuId = t2.SkuId inner join
dbo.Wms_Location AS t3 on t2.LocId = t3.LocId inner join
dbo.Wms_WareHouse AS t4 on t3.StockId = t4.StockId
--2005以上版本
;with t
as(
select px=ROW_NUMBER()over(partition by SkuId,StockId order by id asc)
,* from dbo.Wms_StockLog
)
insert #Wms_StockLogA
select * from t where px=1
;with m
as(
select px=ROW_NUMBER()over(partition by SkuId,StockId order by id desc)
,* from dbo.Wms_StockLogB
)
insert #Wms_StockLog
select * from m
select skuId,StockId
sum(AddedQty) as AddedQty into #Wms_StockLogC
from dbo.Wms_StockLog
WHERE AddedQty>0
group by skuId,StockId
select skuId,StockId
sum(AddedQty) as AddedQty into #Wms_StockLogD
from dbo.Wms_StockLog
WHERE AddedQty<0
group by skuId,StockId
select *
from #Wms_StockLogA a
inner join Wms_StockLogB b
on a.SkuId=b.SkuId and a.StockId=b.StockId
inner join Wms_StockLogC c
on a.SkuId=b.SkuId and a.StockId=c.StockId
inner join Wms_StockLogD d
on a.SkuId=b.SkuId and a.StockId=d.StockId
--2000
select * into #Wms_StockLogA
from Wms_StockLog a
where id=(select MIN(id) from Wms_StockLog b
where a.SkuId=b.SkuId and a.StockId=b.StockId )
select * into #Wms_StockLogb
from Wms_StockLog a
where id=(select MAX(NewQty) from Wms_StockLog b
where a.SkuId=b.SkuId and a.StockId=b.StockId)
select skuId,StockId
sum(AddedQty) as AddedQty into #Wms_StockLogC
from dbo.Wms_StockLog
WHERE AddedQty>0
group by skuId,StockId
select skuId,StockId
sum(AddedQty) as AddedQty into #Wms_StockLogD
from dbo.Wms_StockLog
WHERE AddedQty<0
group by skuId,StockId
select *
from #Wms_StockLogA a
inner join Wms_StockLogB b
on a.SkuId=b.SkuId and a.StockId=b.StockId
inner join Wms_StockLogC c
on a.SkuId=b.SkuId and a.StockId=c.StockId
inner join Wms_StockLogD d
on a.SkuId=b.SkuId and a.StockId=d.StockId
--有语法问题自己调试一下,我这没表结构没法调试
select t0.SkuId , t0.SkuNo , t0.ProductNo, t0.ProductName, t3.StockId, t4.StockName ,
t6.OldQty,t7.NewQty,PeriodIn,PeriodOut
from dbo.Prod_Sku AS t0 inner join dbo.Wms_LocStock AS t2 on t0.SkuId = t2.SkuId
inner join dbo.Wms_Location AS t3 on t2.LocId = t3.LocId
inner join dbo.Wms_WareHouse AS t4 on t3.StockId = t4.StockId
LEFT JOIN
select skuId,StockId,
sum(CASE WHEN AddedQty>0 THEN AddedQty ELSE 0 END ) AS PeriodIn ,
sum(CASE WHEN AddedQty<0 THEN AddedQty ELSE 0 END ) AS PeriodOut ,
from dbo.Wms_StockLog
GROUP BY skuId,StockId
) AS t5 ON t0.skuId=t5.SkuId and t0.StockId=t5.StockId
LEFT JOIN
(
select SkuId, StockId,OldQty from dbo.Wms_StockLog where id=(SELECT min(id) FROM dbo.Wms_StockLog WHERE SkuId=w.SkuId, StockId=w.StockId)
) AS t6 ON t0.skuId=t6.SkuId and t0.StockId=t6.StockId
LEFT JOIN
(
select SkuId, StockId,NewQty from dbo.Wms_StockLog where id=(SELECT max(id) FROM dbo.Wms_StockLog WHERE SkuId=w.SkuId, StockId=w.StockId)
) AS t7 ON t0.skuId=t7.SkuId and t0.StockId=t7.StockId
--试试这个结果对不对
--如果正确,这已是我能尽到的最大能力了
--另外的优化就是索引了
--1、确保你的所有表都有主键
--2、Prod_Sku 表的skuId StockId 有复合索引
--3、Wms_StockLog 表的skuId StockId 有复合索引
消息 102,级别 15,状态 1,第 44 行
'AddedQty' 附近有语法错误。
消息 102,级别 15,状态 1,第 50 行
'AddedQty' 附近有语法错误。
好像应为“select 或者(“
select
t0.SkuId , t0.SkuNo , t0.ProductNo, t0.ProductName, t3.StockId, t4.StockName
---这2个花了21秒
,(select top 1 OldQty from dbo.Wms_StockLog
where SkuId=t0.SkuId and StockId=t3.StockId
order by Id ) as OpeningStock --库存原数量
,(select top 1 NewQty from dbo.Wms_StockLog
where skuId=t0.SkuId and StockId=t3.StockId
order by id desc ) as ClosingStock--库存新数量
---下面2个花了7秒
--AddedQty 必须入库添加的商品数量 》0 必须+的表示 入库 《0 必须-的 出库
,(select sum(AddedQty)from dbo.Wms_StockLog where skuId=t0.SkuId and StockId=t3.StockId
and AddedQty>0) as PeriodIn --入库数量
,(select sum(AddedQty)from dbo.Wms_StockLog where skuId=t0.SkuId and StockId=t3.StockId
and AddedQty<0 )as PeriodOut--出库数量
from dbo.Prod_Sku AS t0 inner join
dbo.Wms_LocStock AS t2 on t0.SkuId = t2.SkuId inner join
dbo.Wms_Location AS t3 on t2.LocId = t3.LocId inner join
dbo.Wms_WareHouse AS t4 on t3.StockId = t4.StockId
;with t
as(
select px=ROW_NUMBER()over(partition by SkuId,StockId order by id asc)
,* from dbo.Wms_StockLog
)
insert #Wms_StockLogA
select * from t where px=1
;with m
as(
select px=ROW_NUMBER()over(partition by SkuId,StockId order by id desc)
,* from dbo.Wms_StockLogB
)
insert #Wms_StockLog
select * from m
select skuId,StockId
sum(AddedQty) into #Wms_StockLogC
from dbo.Wms_StockLog
WHERE AddedQty>0
group by skuId,StockId
select skuId,StockId
sum(AddedQty) into #Wms_StockLogD
from dbo.Wms_StockLog
WHERE AddedQty<0
group by skuId,StockId
select *
from #Wms_StockLogA a
inner join Wms_StockLogB b
on a.SkuId=b.SkuId and a.StockId=b.StockId
inner join Wms_StockLogC c
on a.SkuId=b.SkuId and a.StockId=c.StockId
inner join Wms_StockLogD d
on a.SkuId=b.SkuId and a.StockId=d.StockId
--try
select
t0.SkuId , t0.SkuNo , t0.ProductNo, t0.ProductName, t3.StockId, t4.StockName
---这2个花了21秒
,(select top 1 OldQty from dbo.Wms_StockLog
where SkuId=t0.SkuId and StockId=t3.StockId
order by Id ) as OpeningStock --库存原数量
,(select top 1 NewQty from dbo.Wms_StockLog
where skuId=t0.SkuId and StockId=t3.StockId
order by id desc ) as ClosingStock--库存新数量
---下面2个花了7秒
--AddedQty 必须入库添加的商品数量 》0 必须+的表示 入库 《0 必须-的 出库
,(select sum(AddedQty)from dbo.Wms_StockLog where skuId=t0.SkuId and StockId=t3.StockId
and AddedQty>0) as PeriodIn --入库数量
,(select sum(AddedQty)from dbo.Wms_StockLog where skuId=t0.SkuId and StockId=t3.StockId
and AddedQty<0 )as PeriodOut--出库数量
from dbo.Prod_Sku AS t0 inner join
dbo.Wms_LocStock AS t2 on t0.SkuId = t2.SkuId inner join
dbo.Wms_Location AS t3 on t2.LocId = t3.LocId inner join
dbo.Wms_WareHouse AS t4 on t3.StockId = t4.StockId
;with t
as(
select px=ROW_NUMBER()over(partition by SkuId,StockId order by id asc)
,* from dbo.Wms_StockLog
)
insert #Wms_StockLogA
select * from t where px=1
;with m
as(
select px=ROW_NUMBER()over(partition by SkuId,StockId order by id desc)
,* from dbo.Wms_StockLogB
)
insert #Wms_StockLog
select * from m
select skuId,StockId
sum(AddedQty) into #Wms_StockLogC
from dbo.Wms_StockLog
WHERE AddedQty>0
group by skuId,StockId
select skuId,StockId
sum(AddedQty) into #Wms_StockLogD
from dbo.Wms_StockLog
WHERE AddedQty<0
group by skuId,StockId
select *
from #Wms_StockLogA a
inner join Wms_StockLogB b
on a.SkuId=b.SkuId and a.StockId=b.StockId
inner join Wms_StockLogC c
on a.SkuId=b.SkuId and a.StockId=c.StockId
inner join Wms_StockLogD d
on a.SkuId=b.SkuId and a.StockId=d.StockId
--try
--执行这个语句对比下面的,首先看结果一致不,然后看速度有没有变快
--记得清空缓存
/*
dbcc dropcleanbuffers
dbcc freeproccache
dbcc freesystemcache ('ALL','default');
--[语句执行花费时间(毫秒)]
declare @d datetime
set @d=getdate()
/*你的sql脚本开始*/
/*你的sql脚本结束*/
select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())
*/
--1
select t0.SkuId , t0.SkuNo , t0.ProductNo, t0.ProductName, t3.StockId, t4.StockName
PeriodIn,PeriodOut
from dbo.Prod_Sku AS t0 inner join dbo.Wms_LocStock AS t2 on t0.SkuId = t2.SkuId
inner join dbo.Wms_Location AS t3 on t2.LocId = t3.LocId
inner join dbo.Wms_WareHouse AS t4 on t3.StockId = t4.StockId
INNER JOIN /*如果结果不对,把这里改成left join 试试 */(
select skuId,StockId,
sum(CASE WHEN AddedQty>0 THEN AddedQty ELSE 0 END ) AS PeriodIn ,
sum(CASE WHEN AddedQty<0 THEN AddedQty ELSE 0 END ) AS PeriodOut ,
from dbo.Wms_StockLog
GROUP BY skuId,StockId
) AS t5 ON t0.skuId=t5.SkuId and t0.StockId=t5.StockId
-------------------------------------------------------------
--2
select t0.SkuId , t0.SkuNo , t0.ProductNo, t0.ProductName, t3.StockId, t4.StockName
---下面2个花了7秒
--AddedQty 必须入库添加的商品数量 》0 必须+的表示 入库 《0 必须-的 出库
,(select sum(AddedQty)from dbo.Wms_StockLog where skuId=t0.SkuId and StockId=t3.StockId
and AddedQty>0) as PeriodIn --入库数量
,(select sum(AddedQty)from dbo.Wms_StockLog where skuId=t0.SkuId and StockId=t3.StockId
and AddedQty<0 )as PeriodOut--出库数量
from dbo.Prod_Sku AS t0 inner join dbo.Wms_LocStock AS t2 on t0.SkuId = t2.SkuId
inner join dbo.Wms_Location AS t3 on t2.LocId = t3.LocId
inner join dbo.Wms_WareHouse AS t4 on t3.StockId = t4.StockId