none
优化sql语句 RRS feed

  • 问题

  • update dbo.HC_HouseFollow set Contents='VIP'+Contents where FollowID in (select FollowID from (select FollowID,Contents from (select FollowID,Contents from dbo.HC_HouseFollow inner join dbo.HC_HousingResourcesBase on dbo.HC_HouseFollow.HousingResourcesID=dbo.HC_HousingResourcesBase.HousingResourcesID where HC_HouseFollow.IsDelete=0 and IsVIP=1 and CHARINDEX('VIP',Contents)=0 and PATINDEX('%[0-9]%', Contents)<>1) as a where CHARINDEX('超十天未跟进!系统自动转入认领区!房源编号',Contents)<>0 or CHARINDEX('超十天未跟进!系统自动转入认领区!房源编号',Contents)<>0 or CHARINDEX('新增房源无店长点评!系统自动转入认领区!房源编号',Contents)<>0 or CHARINDEX('认领房源无店长点评!系统自动转入认领区!房源编号',Contents)<>0 or CHARINDEX('跨区转介房源未发生转介!系统自动转入认领区!房源编号',Contents)<>0)as b) update dbo.HC_HouseFollow set Contents=left(Contents,10)+'VIP'+right(Contents,len(Contents)-10) where FollowID in (select FollowID from (select FollowID,Contents from (select FollowID,Contents from dbo.HC_HouseFollow inner join dbo.HC_HousingResourcesBase on dbo.HC_HouseFollow.HousingResourcesID=dbo.HC_HousingResourcesBase.HousingResourcesID where HC_HouseFollow.IsDelete=0 and IsVIP=1 and CHARINDEX('VIP',Contents)=0 and PATINDEX('%[0-9]%', Contents)=1 ) as a where CHARINDEX('超十天未跟进!系统自动转入认领区!房源编号',Contents)<>0 or CHARINDEX('超十天未跟进!系统自动转入认领区!房源编号',Contents)<>0 or CHARINDEX('新增房源无店长点评!系统自动转入认领区!房源编号',Contents)<>0 or CHARINDEX('认领房源无店长点评!系统自动转入认领区!房源编号',Contents)<>0 or CHARINDEX('跨区转介房源未发生转介!系统自动转入认领区!房源编号',Contents)<>0)as b)

    优化sql语句



    please verify my account

    2016年9月9日 1:18

答案

  • 你好,lctk

    如果你是想结合表“HC_HousingResourcesBase”并依据某些条件对表“dbo.HC_HouseFollow”做更新,你可以参考下如下的代码。

    ----方法1------
    update HF
    	SET HF.Contents = left(HF.Contents,10)+'VIP'+right(HF.Contents,len(HF.Contents)-10)
    from dbo.HC_HouseFollow HF
    inner join dbo.HC_HousingResourcesBase HRB
    on HF.HousingResourcesID=HRB.HousingResourcesID
    	where 
    		HF.IsDelete=0 and IsVIP=1 and  CHARINDEX('VIP',HF.Contents)=0  and PATINDEX('%[0-9]%', HF.Contents)=1 
    		and
    		(
    			CHARINDEX('超十天未跟进!系统自动转入认领区!房源编号',HF.Contents)<>0
    			or CHARINDEX('超十天未跟进!系统自动转入认领区!房源编号',HF.Contents)<>0
    			or CHARINDEX('新增房源无店长点评!系统自动转入认领区!房源编号',HF.Contents)<>0
    			or CHARINDEX('认领房源无店长点评!系统自动转入认领区!房源编号',HF.Contents)<>0
    			or CHARINDEX('跨区转介房源未发生转介!系统自动转入认领区!房源编号',HF.Contents)<>0
    		)
    ----方法2------
    ;with cte as
    (
    	select FollowID
    	from dbo.HC_HouseFollow 
    	inner join dbo.HC_HousingResourcesBase on dbo.HC_HouseFollow.HousingResourcesID=dbo.HC_HousingResourcesBase.HousingResourcesID
    	where 
    		HC_HouseFollow.IsDelete=0 and IsVIP=1 and  CHARINDEX('VIP',Contents)=0  and PATINDEX('%[0-9]%', Contents)=1 
    		and
    		(
    			CHARINDEX('超十天未跟进!系统自动转入认领区!房源编号',Contents)<>0
    			or CHARINDEX('超十天未跟进!系统自动转入认领区!房源编号',Contents)<>0
    			or CHARINDEX('新增房源无店长点评!系统自动转入认领区!房源编号',Contents)<>0
    			or CHARINDEX('认领房源无店长点评!系统自动转入认领区!房源编号',Contents)<>0
    			or CHARINDEX('跨区转介房源未发生转介!系统自动转入认领区!房源编号',Contents)<>0
    		)
    )
    
    update dbo.HC_HouseFollow
    set Contents=left(Contents,10)+'VIP'+right(Contents,len(Contents)-10)
    where exists (select 1 from cte where HC_HouseFollow.FollowID = cte.FollowID)
    

    Best Regards,

    Albert Zhang

    • 已标记为答案 lctk 2016年9月10日 0:36
    2016年9月9日 7:45

全部回复

  • 光看 CHARINDEX 就已经觉得没救了
    2016年9月9日 4:46
  • 怎么修改?

    please verify my account

    2016年9月9日 4:53
  • 你好,lctk

    如果你是想结合表“HC_HousingResourcesBase”并依据某些条件对表“dbo.HC_HouseFollow”做更新,你可以参考下如下的代码。

    ----方法1------
    update HF
    	SET HF.Contents = left(HF.Contents,10)+'VIP'+right(HF.Contents,len(HF.Contents)-10)
    from dbo.HC_HouseFollow HF
    inner join dbo.HC_HousingResourcesBase HRB
    on HF.HousingResourcesID=HRB.HousingResourcesID
    	where 
    		HF.IsDelete=0 and IsVIP=1 and  CHARINDEX('VIP',HF.Contents)=0  and PATINDEX('%[0-9]%', HF.Contents)=1 
    		and
    		(
    			CHARINDEX('超十天未跟进!系统自动转入认领区!房源编号',HF.Contents)<>0
    			or CHARINDEX('超十天未跟进!系统自动转入认领区!房源编号',HF.Contents)<>0
    			or CHARINDEX('新增房源无店长点评!系统自动转入认领区!房源编号',HF.Contents)<>0
    			or CHARINDEX('认领房源无店长点评!系统自动转入认领区!房源编号',HF.Contents)<>0
    			or CHARINDEX('跨区转介房源未发生转介!系统自动转入认领区!房源编号',HF.Contents)<>0
    		)
    ----方法2------
    ;with cte as
    (
    	select FollowID
    	from dbo.HC_HouseFollow 
    	inner join dbo.HC_HousingResourcesBase on dbo.HC_HouseFollow.HousingResourcesID=dbo.HC_HousingResourcesBase.HousingResourcesID
    	where 
    		HC_HouseFollow.IsDelete=0 and IsVIP=1 and  CHARINDEX('VIP',Contents)=0  and PATINDEX('%[0-9]%', Contents)=1 
    		and
    		(
    			CHARINDEX('超十天未跟进!系统自动转入认领区!房源编号',Contents)<>0
    			or CHARINDEX('超十天未跟进!系统自动转入认领区!房源编号',Contents)<>0
    			or CHARINDEX('新增房源无店长点评!系统自动转入认领区!房源编号',Contents)<>0
    			or CHARINDEX('认领房源无店长点评!系统自动转入认领区!房源编号',Contents)<>0
    			or CHARINDEX('跨区转介房源未发生转介!系统自动转入认领区!房源编号',Contents)<>0
    		)
    )
    
    update dbo.HC_HouseFollow
    set Contents=left(Contents,10)+'VIP'+right(Contents,len(Contents)-10)
    where exists (select 1 from cte where HC_HouseFollow.FollowID = cte.FollowID)
    

    Best Regards,

    Albert Zhang

    • 已标记为答案 lctk 2016年9月10日 0:36
    2016年9月9日 7:45