34,575
社区成员
发帖
与我相关
我的任务
分享
Select
(select top 1 cfe1 from a_spmx where spbh=test1.spbh and ckbh=test1.ckbh and scph=test1.scph and dwid=test1.dwid and isnull(cfe1,'')<>'') cfe1
,(select top 1 cfe3 from a_spmx where spbh=test1.spbh and ckbh=test1.ckbh and scph=test1.scph and dwid=test1.dwid and isnull(cfe3,'')<>'') cfe3
,(select top 1 cfe4 from a_spmx where spbh=test1.spbh and ckbh=test1.ckbh and scph=test1.scph and dwid=test1.dwid and isnull(cfe4,'')<>'') cfe4
From
V_BAT
test1
LEFT JOIN D_CK C ON C.CKBH=test1.CKBH
LEFT JOIN D_SP S ON S.SPBH=test1.SPBH
Where
C.CKBH LIKE '%'
and S.SPBH LIKE '%'
and test1.dwid like '0%'
所有的问题都是这个查询造成
test1 是一个随便命名的虚表SELECT
GFMC 供方名称,
仓库,
商品,
其它二,
批号,
SPSL,
DFE1,
每根重量
,CFE1
,CFE3
,CFE4
FROM
(Select
CKMC 仓库,
s.spmc 品号,
SPMC 商品,
ZHBZ 其它二,
SCPH 批号,
YESL+isnull((select sum(spsl) from k_rkw w left join k_rk t on w.link=t.link where bstt=0 and ckbh=v.ckbh and spbh=v.spbh and scph=v.scph),0) SPSL,
YESL1+isnull((select sum(w.dfe1) from k_rkw w left join k_rk t on w.link=t.link where bstt=0 and ckbh=v.ckbh and spbh=v.spbh and scph=v.scph),0) DFE1,
YEJE+isnull((select sum(spje) from k_rkw w left join k_rk t on w.link=t.link where bstt=0 and ckbh=v.ckbh and spbh=v.spbh and scph=v.scph),0) 库存成本,
isnull((select top 1 agsl from k_rkw w left join k_rk t on w.link=t.link where ckbh=v.ckbh and spbh=v.spbh and scph=v.scph),0) 每根重量,
isnull((select top 1 GFBH from k_rkw w left join k_rk t on w.link=t.link where ckbh=v.ckbh and spbh=v.spbh and scph=v.scph order by t.line desc),0) GFBH
,(select top 1 cfe1 from a_spmx where spbh=V.spbh and ckbh=V.ckbh and scph=V.scph and dwid=v.dwid and isnull(cfe1,'')<>'') cfe1
,(select top 1 cfe3 from a_spmx where spbh=V.spbh and ckbh=V.ckbh and scph=V.scph and dwid=v.dwid and isnull(cfe3,'')<>'') cfe3
,(select top 1 cfe4 from a_spmx where spbh=V.spbh and ckbh=V.ckbh and scph=V.scph and dwid=v.dwid and isnull(cfe4,'')<>'') cfe4
-- 注释掉上面三个查询速度就正常,不注释掉注释就很慢
From V_BAT V
LEFT JOIN D_CK C ON C.CKBH=V.CKBH
LEFT JOIN D_SP S ON S.SPBH=V.SPBH
Where
C.CKBH LIKE '%'
and S.SPBH LIKE '%'
and v.dwid like '0%'
)
A LEFT JOIN D_GYS S ON A.GFBH=S.GFBH
WHERE
A.GFBH LIKE '%'
and round(dfe1,4)<>0