常用SQL语句

1：创建BOM展开视图

Create VIEW  V_K3_BOM

AS 

select distinct

BOM.FID BOMID, BOM.FMATERIALID PFMATERIALID,MATERIAL_L.FNAME PFMATERIALFNAME,MATERIAL.fnumber PFMATERIALFNUMBER,MATERIALBase.FERPCLSID PFERPCLSID,

BOMCHILD.FMATERIALID subFMATERIALID,subMATERIAL.FNUMBER subMATERIALFNUMBER,MATERIAL_L2.FNAME subMATERIALFNAME,subMATERIALBase.FERPCLSID subFERPCLSID,

BOM.FNUMBER BOMFNUMBER,BOM.FUSEORGID FUSEORGID,ORGANIZATIONS.FNUMBER ORGFNUMBER,ORGANIZATIONS_L.FNAME ORGFNAME,BOM.FDOCUMENTSTATUS FDOCUMENTSTATUS

,BOMCHILD.FUQTY,BOM.F_KD_MAINPRODUCTNO,BOM.F_KD_INTEGER

from T_ENG_BOM BOM

inner join T_ENG_BOMCHILD BOMCHILD  

on BOMCHILD.FID=BOM.FID

inner join T_BD_MATERIAL  MATERIAL 

on MATERIAL.FMATERIALID=BOM.FMATERIALID

inner join t_BD_MaterialBase MATERIALBase  

on MATERIALBase.FMATERIALID=BOM.FMATERIALID

inner join t_BD_MaterialBase subMATERIALBase  

on subMATERIALBase.FMATERIALID=BOMCHILD.FMATERIALID

inner join T_BD_MATERIAL  subMATERIAL

on subMATERIAL.FMATERIALID=BOMCHILD.FMATERIALID

inner join T_BD_MATERIAL_L MATERIAL_L

on (MATERIAL_L.FMATERIALID=BOM.FMATERIALID and MATERIAL_L.FLOCALEID='2052')

inner join T_BD_MATERIAL_L MATERIAL_L2

on (MATERIAL_L2.FMATERIALID=BOMCHILD.FMATERIALID and MATERIAL_L2.FLOCALEID='2052')

inner join T_ORG_ORGANIZATIONS_L ORGANIZATIONS_L

on (ORGANIZATIONS_L.FORGID=BOM.FUSEORGID and ORGANIZATIONS_L.FLOCALEID='2052')

inner join T_ORG_ORGANIZATIONS ORGANIZATIONS 

on ORGANIZATIONS.FORGID=BOM.FUSEORGID;

2：存储过程传入字符串后需要拆分：例如‘a,b,c’

1)拆分： 

函数一

create  function  SplitIn(@c   varchar (2000),@split   varchar (2))    

returns   @t   table (col   varchar (20))    

as    

begin     

   while(charindex(@split,@c)<>0)    

     begin    

       insert   @t(col)   values   ( substring (@c,1,charindex(@split,@c)-1))    

       set   @c   =   stuff(@c,1,charindex(@split,@c), '' )    

     end    

   insert   @t(col)   values   (@c)    

   return    

end

函数二：

create function [dbo].[f_split](@SourceSql varchar(max),@StrSeprate varchar(10))

    returns @temp table(Rowvalue varchar(1000))

as

    begin

        declare @i int

        set @SourceSql=rtrim(ltrim(@SourceSql))

        set @i=charindex(@StrSeprate,@SourceSql)

        while @i>=1

        begin

            insert @temp values(left(@SourceSql,@i-1))

            set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)

            set @i=charindex(@StrSeprate,@SourceSql)

        end

        if @SourceSql<>''

        insert @temp values(@SourceSql)

     return

    end