两则实现相同功能的代码比较(使用临时表与不用临时表比较)

if (not exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[old_outid]') and objectproperty(id, n'isusertable') = 1))
begin
 select customerid,outid into old_outid from t_customers
 --update  t_customers set outid='请输入新的学工号'
/* --把新工号恢复成老工号值
update t_customers
set t_customers.outid = old_outid.outid
from old_outid
where old_outid.customerid = t_customers.customerid
*/
end

select *  into #dpt from (select  dpcode1+dpcode2+dpcode3 as 部门代码,dpname1+'/'+dpname2+'/'+dpname3 as 部门名称  from t_department where dpname1 is not null and dpname2 is not null and dpname3 is not null union all select dpcode1+dpcode2+dpcode3 as 部门代码,dpname1+'/'+dpname2 as 部门名称 from t_department where dpname1 is not null and dpname2 is not null and dpname3 is  null union all select  dpcode1+dpcode2+dpcode3 as 部门代码,dpname1 as 部门名称 from t_department where dpname1 is not null and dpname2 is  null and dpname3 is  null) l

select customerid, max(opdt) as 最近一次补卡时间 into #renewcard_maxdt
from t_renewcard
group by customerid

select 部门名称,name as 姓名, alias as 别名, cardtype as 卡类, c.outid as 新工号,o.outid as 旧工号,最近一次补卡时间
from t_customers c inner join #dpt d on d.部门代码=c.account inner join #renewcard_maxdt r on
r.customerid=c.customerid inner join old_outid o on c.customerid=o.customerid
where (c.cardtype in (1, 2))

drop table #dpt
drop table #renewcard_maxdt

/* --作视图代码，功能同上，不便阅读理解与修改更新,但适用广。
select d.部门名称, c.name as 姓名, c.alias as 别名, c.cardtype as 卡类,
      c.outid as 新工号, o.outid as 旧工号, r.最近一次补卡时间
from t_customers c inner join
          (select dpcode1 + dpcode2 + dpcode3 as 部门代码,
               dpname1 + '/' + dpname2 + '/' + dpname3 as 部门名称
         from t_department
         where dpname1 is not null and dpname2 is not null and
               dpname3 is not null
         union all
         select dpcode1 + dpcode2 + dpcode3 as 部门代码,
               dpname1 + '/' + dpname2 as 部门名称
         from t_department
         where dpname1 is not null and dpname2 is not null and
               dpname3 is null
         union all
         select dpcode1 + dpcode2 + dpcode3 as 部门代码, dpname1 as 部门名称
         from t_department
         where dpname1 is not null and dpname2 is null and dpname3 is null)
      d on d.部门代码 = c.account inner join
          (select customerid, max(opdt) as 最近一次补卡时间
         from t_renewcard
         group by customerid) r on r.customerid = c.customerid inner join
      old_outid o on c.customerid = o.customerid
where (c.cardtype in (1, 2))
*/