sql not like 数据遗漏

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()