select as table 運用

<br />set nocount on<br />declare @ntime datetime<br />declare @stime varchar(19)<br />declare @etime varchar(19)<br /><br />set @ntime = dateadd(d,-1,getdate())<br />set @stime = convert(varchar(10),@ntime,120) + ' 00:00:00'<br />set @etime = convert(varchar(10),@ntime,120) + ' 23:59:59'<br /><br />-- *************************************************************<br /><br />select a.custid, count(a.custid) as cnt<br />--into #custid_cnt_tmp<br />from<br />(select b.custid, b.cphone, b.mphone, b.ophone<br />from car_mem_cnt_tab a inner join car_cust_tab b<br />on a.custid = b.custid<br />where a.cnt_grp = 0) as a<br />inner join<br />(select a.custid, isnull(a.callerid,'') as callerid, a.workid<br />from car_workid_tab a<br />where a.createtime between @stime and @etime<br />and dbo.fn_wstate(a.cepresult, a.agtresult,'0') = 1<br />and left(a.callerid,2) = '09' -- 一定要手機來電<br />) as b<br />on a.custid = b.custid<br />where b.callerid in (a.cphone, a.mphone, a.ophone)<br />group by a.custid<br />-- STEP 2<br />update car_mem_cnt_tab<br />set cnt = isnull(a.cnt,0) + b.cnt,<br />utime = getdate()<br />from car_mem_cnt_tab a inner join #custid_cnt_tmp b<br />on a.custid = b.custid<br />where a.cnt_grp = 0<br />-- STEP 3<br />drop table #custid_cnt_tmp<br />