SELECT zdrq 日期,djbh 单号,c.ckbh 仓库编号,CKMC 仓库,H.CFE1 提货方式,H.CFE2 付款方式,
(select top 1 djbh from k_ckw cw left join k_ck ct on cw.link=ct.link where cw.fhid=w.id) 出库单号,
k.khbh 客户编号,K.KHMC AS 客户名称,y.ywbh 业务编号,Y.YWXM AS 业务员 ,W.SPBH AS 商品编号,P.SPMC AS 商品名称,W.GGXH AS 规格型号,
P.JLDW 单位,P.SPCD ,P.SPCZ ,p.spbz ,p.splb,p.gsz 根实重,
w.hwbh,W.SCPH 入库批号,
w.cfe1,w.cfe2,w.cfe3,w.cfe4,w.cfe5,w.cfe6,w.cfe7,w.cfe8,
case when H.FHFS=0 THEN '合同销售'
when H.FHFS=1 THEN '正常销售'
when H.FHFS=2 THEN '内调销售'
when H.FHFS=3 THEN '直销销售'
when H.FHFS=4 THEN '其它销售'
when H.FHFS=5 THEN '退货销售'
else '' end 销售类别,
ISNULL(W.SPSL,0) AS SPSL
,ISNULL(W.DFE1,0) AS DFE1
,ISNULL(W.DFE2,0) AS DFE2
,ISNULL(W.DFE3,0) AS DFE3
,DBO.ISZERO(W.SPJE+W.SSSE,W.SPSL) AS SPSL价
,DBO.ISZERO(W.SPJE+W.SSSE,W.DFE1) AS DFE1价
,DBO.ISZERO(W.SPJE+W.SSSE,W.DFE2) AS DFE2价
,DBO.ISZERO(W.SPJE+W.SSSE,W.DFE3) AS DFE3价
--,isnull(dbo.getFHFYDJ(w.link,2),0) 费用单价
--,isnull(dbo.getFHFYDJ(w.link,2)*w.spsl,0) 费用金额
,ISNULL(W.SPJE,0) AS SPJE
,ISNULL(W.SSSE,0) AS 税额
,ISNULL(W.SSSE+W.SPJE,0) AS JESE
--,ISNULL(W.SSSE+W.SPJE,0)+isnull(dbo.getFHFYDJ(w.link,2)*w.spsl,0) AS 金额总计
,isnull(w.SPCB,0) as SPSL成本价
,dbo.iszero(isnull(w.SPcb*w.spsl,0),isnull(W.DFE1,0)) as DFE1成本价
,dbo.iszero(isnull(w.SPcb*w.spsl,0),isnull(W.DFE2,0)) as DFE2成本价
,dbo.iszero(isnull(w.SPcb*w.spsl,0),isnull(W.DFE3,0)) as DFE3成本价
,isnull(w.SPcb*w.spsl,0) as 成本SPJE
,isnull((select sum(spsl) from k_ckw where fhid=w.id),0) 出库数量
,isnull((select sum(dfe1) from k_ckw where fhid=w.id),0) 出库支数
,isnull((select sum(dfe2) from k_ckw where fhid=w.id),0) 出库过磅
,isnull((select sum(dfe3) from k_ckw where fhid=w.id),0) 出库件数
,isnull((select sum(shsl) from k_ckw where fhid=w.id),0) 收货数量
,isnull((select sum(shhl) from k_ckw where fhid=w.id),0) 收货支数
,isnull((select sum(shtl) from k_ckw where fhid=w.id),0) 收货过磅
,isnull((select sum(shfl) from k_ckw where fhid=w.id),0) 收货件数
,isnull(w.spje-w.spcb*w.spsl,0) as 利润1
,isnull(w.spje-(select sum(spje) from k_ckw where fhid=w.id),0) as 利润2
,(select top 1 dt.cfe3 from c_dd dt left join c_ddw dw on dt.link=dw.link where dw.id in(select ddid from k_rkw where spbh=w.spbh and scph=w.scph)) 采购合同编号
,(select top 1 dt.cfe3 from x_dd dt left join x_ddw dw on dt.link=dw.link where dw.id =ddid) 销销合同编号,
'X_FH' SRC,
W.LINK SRCID,
W.ID WID,W.LINE 生成时间
FROM
X_FHW W LEFT JOIN X_FH H ON W.LINK=H.LINK
LEFT JOIN D_SP P ON W.SPBH=P.SPBH
LEFT JOIN D_KH K ON H.KHBH=K.KHBH
LEFT JOIN D_YWY Y ON Y.YWBH=H.YWBH
left join d_ck c on c.ckbh=w.ckbh
WHERE ZDRQ >= '[开始日期,DATE]' AND ZDRQ <= '[结束日期,DATE]'
and H.DWID like '[公司,D_DW]%'
AND W.SPBH LIKE '[商品名称,D_SP]%' AND
H.KHBH LIKE '[客户名称,D_KH]%' AND BSTT >= 1
AND MMMM<>0
AND (H.YWBH LIKE '[业务员,D_YWY]%' OR H.YWBH IS NULL)
AND W.CKBH LIKE '[仓库,D_CK]%'
查询慢，感觉是列求和的问题
------解决方案--------------------
- SQL code
SELECT zdrq 日期, djbh 单号, c.ckbh 仓库编号, CKMC 仓库, H.CFE1 提货方式, H.CFE2 付款方式, ( select top 1 djbh from k_ckw cw left join k_ck ct on cw.link=ct.link where cw.fhid=w.id ) 出库单号, k.khbh 客户编号, K.KHMC AS 客户名称, y.ywbh 业务编号, Y.YWXM AS 业务员, W.SPBH AS 商品编号, P.SPMC AS 商品名称, W.GGXH AS 规格型号, P.JLDW 单位, P.SPCD , P.SPCZ , p.spbz , p.splb, p.gsz 根实重, w.hwbh, W.SCPH 入库批号, w.cfe1, w.cfe2, w.cfe3, w.cfe4, w.cfe5, w.cfe6, w.cfe7, w.cfe8, case when H.FHFS=0 THEN '合同销售' when H.FHFS=1 THEN '正常销售' when H.FHFS=2 THEN '内调销售' when H.FHFS=3 THEN '直销销售' when H.FHFS=4 THEN '其它销售' when H.FHFS=5 THEN '退货销售' else '' end 销售类别, ISNULL(W.SPSL,0) AS SPSL ,ISNULL(W.DFE1,0) AS DFE1 ,ISNULL(W.DFE2,0) AS DFE2 ,ISNULL(W.DFE3,0) AS DFE3 ,DBO.ISZERO(W.SPJE+W.SSSE,W.SPSL) AS SPSL价 ,DBO.ISZERO(W.SPJE+W.SSSE,W.DFE1) AS DFE1价 ,DBO.ISZERO(W.SPJE+W.SSSE,W.DFE2) AS DFE2价 ,DBO.ISZERO(W.SPJE+W.SSSE,W.DFE3) AS DFE3价 ,ISNULL(W.SPJE,0) AS SPJE ,ISNULL(W.SSSE,0) AS 税额 ,ISNULL(W.SSSE+W.SPJE,0) AS JESE ,isnull(w.SPCB,0) as SPSL成本价 ,dbo.iszero(isnull(w.SPcb*w.spsl,0),isnull(W.DFE1,0)) as DFE1成本价 ,dbo.iszero(isnull(w.SPcb*w.spsl,0),isnull(W.DFE2,0)) as DFE2成本价 ,dbo.iszero(isnull(w.SPcb*w.spsl,0),isnull(W.DFE3,0)) as DFE3成本价 ,isnull(w.SPcb*w.spsl,0) as 成本SPJE ,k_c.spsl as 出库数量 ,k_c.dfe1 as 出库支数 ,k_c.dfe2 as 出库过磅 ,k_c.dfe3 as 出库件数 ,k_c.shsl as 收货数量 ,k_c.shhl as 收货支数 ,k_c.shtl as 收货过磅 ,k_c.shfl as 收货件数 ,isnull(w.spje-w.spcb*w.spsl,0) as 利润1 ,isnull(w.spje-k_c.spje),0) as 利润2 ,( select top 1 dt.cfe3 from c_dd dt left join c_ddw dw on dt.link=dw.link where exists ( select 1 from k_rkw where spbh=w.spbh and scph=w.scph and ddid=dw.id ) ) 采购合同编号 ,( select top 1 dt.cfe3 from x_dd dt left join x_ddw dw on dt.link=dw.link where dw.id =ddid ) 销销合同编号, 'X_FH' SRC, W.LINK SRCID, W.ID WID,W.LINE 生成时间FROM X_FHW W LEFT JOIN X_FH H ON W.LINK=H.LINK LEFT JOIN D_SP P ON W.SPBH=P.SPBH LEFT JOIN D_KH K ON H.KHBH=K.KHBH LEFT JOIN D_YWY Y ON Y.YWBH=H.YWBH left join d_ck c on c.ckbh=w.ckbh left join ( select fhid, isnull(sum(spsl),0) as spsl, isnull(sum(dfe1),0) as dfe1, isnull(sum(dfe2),0) as dfe2, isnull(sum(dfe3),0) as dfe3, isnull(sum(shsl),0) as shsl, isnull(sum(shhl),0) as shhl, isnull(sum(shtl),0) as shtl, isnull(sum(shfl),0) as shfl, isnull(sum(spje),0) as spje from k_ckw group by fhid ) k_c on k_c.fhid=w.idWHERE ZDRQ >= '[开始日期,DATE]' AND ZDRQ <= '[结束日期,DATE]' and H.DWID like '[公司,D_DW]%' AND W.SPBH LIKE '[商品名称,D_SP]%' AND H.KHBH LIKE '[客户名称,D_KH]%' AND BSTT >= 1 AND MMMM<>0 AND (H.YWBH LIKE '[业务员,D_YWY]%' OR H.YWBH IS NULL) AND W.CKBH LIKE '[仓库,D_CK]%'--采购合同编号 和 销销合同编号 也可以放到 from 后面，通过Left Join 实现, 再者 where 后面能不使用like就不使用...