sql 临时表与子查询效率解析
update DC_Keyword set wordStatus='已启用' where word = '类目出价' and wordStatus=''
select a.data_time, a.campaign_id, a.adgroup_id, a.nick, b.word, b.wordStatus, --a.isUseCategoryPrice, case b.wordStatus when '未启用' then 0 else 1 end NewIsUseCategoryPrice INTO #TEMP111 from DC_ADGroup a inner join DC_Keyword b on a.data_time=b.data_time and a.campaign_id=b.campaign_id and a.adgroup_id=b.adgroup_id where b.word = '类目出价' update DC_ADGroup set isUseCategoryPrice= BB.NewIsUseCategoryPrice --select --AA.data_time, --AA.campaign_id, --AA.adgroup_id, --AA.nick, --BB.word, --BB.wordStatus, --AA.isUseCategoryPrice, --BB.NewIsUseCategoryPrice from DC_ADGroup AA inner join #TEMP111 BB --注意:如果这里不用临时表,当执行大数据量时,效率很差 ON AA.data_time=BB.data_time AND AA.nick=BB.nick AND AA.campaign_id=BB.campaign_id AND AA.adgroup_id=BB.adgroup_id
--不建议用下面这种方式:
update DC_Keyword set wordStatus='已启用' where word = '类目出价' and wordStatus=''
update DC_ADGroup set isUseCategoryPrice= BB.NewIsUseCategoryPrice --select --AA.data_time, --AA.campaign_id, --AA.adgroup_id, --AA.nick, --BB.word, --BB.wordStatus, --AA.isUseCategoryPrice, --BB.NewIsUseCategoryPrice from DC_ADGroup AA inner join
(
select a.data_time, a.campaign_id, a.adgroup_id, a.nick, b.word, b.wordStatus, --a.isUseCategoryPrice, case b.wordStatus when '未启用' then 0 else 1 end NewIsUseCategoryPrice INTO #TEMP111 from DC_ADGroup a inner join DC_Keyword b on a.data_time=b.data_time and a.campaign_id=b.campaign_id and a.adgroup_id=b.adgroup_id where b.word = '类目出价'
) BB
ON AA.data_time=BB.data_time AND AA.nick=BB.nick AND AA.campaign_id=BB.campaign_id AND AA.adgroup_id=BB.adgroup_id