sql 创建临时表

 declare @channelid varchar(100)
 set @channelid='''WH00026'',''WH00083'''
 declare @sql varchar(1000)
 if(object_id('tempdb.dbo.#ac') is not null)
 begin
	drop table #ac
 end
 create table #ac
 (
	channelid varchar(50),
	pmoney decimal(18,2),
	ptype tinyint,
	inday varchar(10),
	scale varchar(10),
	comScale varchar(10)
 )
 set @sql='insert into #ac'
 set @sql=@sql+' select a.channelid,a.pmoney,a.ptype,a.inday,a.scale,a.comScale'
 set @sql=@sql+' from('
 set @sql=@sql+' select oc.channelid,sum(totalFee) as pmoney,inday,scale,comScale,1 as ptype from orderCPS oc'
 set @sql=@sql+' left join userInfo u on oc.channelid=u.channelid'
 set @sql=@sql+' where inday between ''2016-10-13'' and ''2016-10-13'' and oc.channelid in ('+@channelid+') and (comOrderStatus is null or comOrderStatus=0)' 
 set @sql=@sql+' group by oc.channelid,inday,scale,comScale'
 set @sql=@sql+' union all'
 set @sql=@sql+' select oc.channelid,sum(totalFee) as pmoney,inday,scale,comScale,2 as ptype from orderCPS oc'
 set @sql=@sql+' left join userInfo u on oc.channelid=u.channelid'
 set @sql=@sql+' where inday between ''2016-10-13'' and ''2016-10-13'' and oc.channelid in ('+@channelid+') and orderStatus=0'
 set @sql=@sql+' group by oc.channelid,inday,scale,comScale) a'
 print @sql
 exec(@sql);
 select * from #ac
　　

　　

转载于:https://www.cnblogs.com/mrzhoushare/p/5961119.html