not like 数据遗漏
在一NULL列,两者LIKE并NOT LIKE反对任何搜索字符串将返回NULL。
方案一
可以使用not in + like :
select
(case when t3_L.FSHORTNAME=null then t3_L.FNAME else t3_L.FSHORTNAME end)as custName ,t3_G.FNAME as custGroup,
(case when sup_L.FSHORTNAME=null then sup_L.FNAME else sup_L.FSHORTNAME end)as supName
,t3.FUSEORGID,t4_CP.F_ADD_TEXT221 as zykh ,t4.FNUMBER materialNumber,sum(ISNULL(t2.FREALQTY,0)-ISNULL(t6_EN.FREALQTY,0)) as qty ,t4.F_ADD_TEXT11 as AL,t4_CP.fnumber CPWLBM,org_L.FNAME as orgName
,t3.F_ADD_TEXT as khfz2,T3.F_ADD_REMARK glkh,t3_G.FNAME as khfz,dq.FDATAVALUE as dq ,t4_CP.F_ADD_TEXT as cpcc,t7.FF100005 xscc
,(case when t4_CP.F_ADD_TEXT<>''and t7.FF100005<>''and charindex('*',t7.FF100005)>0 and charindex('*',t4_CP.F_ADD_TEXT)>0
then ((CONVERT(decimal,isnull(left(t4_CP.F_ADD_TEXT,PATINDEX('%*%',t4_CP.F_ADD_TEXT)-1),0))
* CONVERT(decimal,isnull(RIGHT(t4_CP.F_ADD_TEXT,len(t4_CP.F_ADD_TEXT)- PATINDEX('%*%',t4_CP.F_ADD_TEXT) ),0))) )
/((CONVERT(decimal,isnull(left(t7.FF100005,PATINDEX('%*%',t7.FF100005)-1),0))
* CONVERT(decimal,isnull(RIGHT(t7.FF100005,len(t7.FF100005)- PATINDEX('%*%',t7.FF100005)),0))))
*sum(ISNULL(t2.FREALQTY,0)-ISNULL(t6_EN.FREALQTY,0)) else 0 end) as bzzh
from T_SAL_OUTSTOCK t1
left join T_SAL_OUTSTOCKENTRY t2 on t1.Fid=t2.fid
left join T_BD_CUSTOMER t3 on t1.fcustomerid=t3.FCUSTID
left join T_BD_CUSTOMER_L t3_L on t3.FCUSTID=t3_L.FCUSTID and t3_L.FLOCALEID=2052
left join T_ENG_BOM bom on t2.FMATERIALID=bom.FMATERIALID
left join T_ENG_BOMCHILD bomc on bomc.FID=bom.FID
left join T_BD_MATERIAL t4 on bomc.FMATERIALID=t4.fmaterialid
left join T_BD_MATERIAL t4_CP on t4_CP.FMATERIALID=t2.FMATERIALID
left join t_bd_MaterialPurchase t4_P on t4.FMATERIALID=t4_P.fmaterialid
left join t_BD_Supplier sup on sup.FSUPPLIERID=t4_P.FDEFAULTVENDORID
left join T_BD_SUPPLIER_L sup_L on sup.FSUPPLIERID=sup_L.FSUPPLIERID
left join T_BD_MATERIAL_L t4_L on t4.FMATERIALID=t4_L.fmaterialid and t4_L.FLOCALEID=2052
left join T_ORG_ORGANIZATIONS_L t5_L on t1.FSTOCKORGID=t5_L.FORGID and t5_L.FLOCALEID=2052
left join T_BD_CUSTOMERGROUP_L t3_G on t3. FPRIMARYGROUP=t3_G.FID
left join t_org_organizations_L org_L on t1.FSTOCKORGID=org_L.FORGID and org_L.FLOCALEID=2052
left join T_BAS_ASSISTANTDATAENTRY_L dq on t3.FPROVINCIAL=dq.FENTRYID and dq.FLOCALEID=2052
left join T_SAL_RETURNSTOCK t6 on t1.fcustomerid=t6.FRETCUSTID and t1.FSTOCKORGID=t6.FSTOCKORGID
left join T_SAL_RETURNSTOCKENTRY t6_EN on t6.fid=t6_EN.fid and t6_EN.fmaterialID=t4_CP.FMATERIALID
left join T_BD_FLEXSITEMDETAILV t7 on t2.fAuxPropId=t7.FID
where t1.FRECEIVEADDRESS<>''and YEAR(t1.FDATE)like '2022%' and MONTH(t1.FDATE) like '9%' and t1.FSTOCKORGID like '100001%'
and t4.FNUMBER not in ( select t4.FNUMBER materialNumber
from T_SAL_OUTSTOCK t1
left join T_SAL_OUTSTOCKENTRY t2 on t1.FRECEIVEADDRESS<>''and YEAR(t1.FDATE)like '2022%' and MONTH(t1.FDATE) like '9%' and t1.FSTOCKORGID like '100001%' and t1.Fid=t2.fid
left join T_BD_CUSTOMER t3 on t3.fnumber like '%' and t1.fcustomerid=t3.FCUSTID
left join T_BD_CUSTOMER_L t3_L on t3.FCUSTID=t3_L.FCUSTID and t3_L.FLOCALEID=2052
left join T_ENG_BOM bom on t2.FMATERIALID=bom.FMATERIALID
left join T_ENG_BOMCHILD bomc on bomc.FID=bom.FID
left join T_BD_MATERIAL t4 on t4.F_ADD_TEXT221 like '%%' and bomc.FMATERIALID=t4.fmaterialid
left join T_BD_MATERIAL t4_CP on t4_CP.fnumber like '%%' and t4_CP.FMATERIALID=t2.FMATERIALID
left join t_bd_MaterialPurchase t4_P on t4.FMATERIALID=t4_P.fmaterialid
left join t_BD_Supplier sup on sup.FSUPPLIERID=t4_P.FDEFAULTVENDORID
left join T_BD_SUPPLIER_L sup_L on sup.FSUPPLIERID=sup_L.FSUPPLIERID
left join T_BD_MATERIAL_L t4_L on t4.FMATERIALID=t4_L.fmaterialid and t4_L.FLOCALEID=2052
left join T_ORG_ORGANIZATIONS_L t5_L on t1.FSTOCKORGID=t5_L.FORGID and t5_L.FLOCALEID=2052
left join T_BD_CUSTOMERGROUP_L t3_G on t3. FPRIMARYGROUP=t3_G.FID
left join t_org_organizations_L org_L on t1.FSTOCKORGID=org_L.FORGID and org_L.FLOCALEID=2052
left join T_BAS_ASSISTANTDATAENTRY_L dq on t3.FPROVINCIAL=dq.FENTRYID and dq.FLOCALEID=2052
left join T_SAL_RETURNSTOCK t6 on t1.fcustomerid=t6.FRETCUSTID and t1.FSTOCKORGID=t6.FSTOCKORGID
left join T_SAL_RETURNSTOCKENTRY t6_EN on t6.fid=t6_EN.fid and t6_EN.fmaterialID=t4_CP.FMATERIALID
left join T_BD_FLEXSITEMDETAILV t7 on t2.fAuxPropId=t7.FID
where t4_CP.fnumber like '56%' or t4_CP.fnumber like '46%' or t4_CP.fnumber ='94-SSJZ0000-05'
group by t4.FNUMBER
)
group by t3_L.FSHORTNAME,t3_G.FNAME, t3_L.fname ,t3.FUSEORGID,t4_CP.F_ADD_TEXT221,t4.FNUMBER,sup_L.FSHORTNAME
,t4.F_ADD_TEXT11,t4_CP.FNUMBER,org_L.FNAME,t3.F_ADD_TEXT ,T3.F_ADD_REMARK ,t3_G.FNAME,dq.FDATAVALUE ,t4_CP.F_ADD_TEXT,t7.FF100005
方案二
或者是使用 not exists()