with as 和 临时表的区别。

1.with as 

with as 只是把子查询的语句当作了一个表，但是真实的数据并没有插入到数据库，它的好处是增加了代码的可读性和进行维护。

--设计累计发生
with ljfssj as (select b.ProjGUID,COUNT(*) Ljfscs,										--累计发生次数SUM(ISNULL(a.ApproveAmount_Bz,0)) ljfsAmount			--累计发生金额from cb_DesignAlterToContract aleft join cb_DesignAlter b on a.DesignAlterGuid = b.DesignAlterGUIDwhere b.ApproveStatusEnum = 3 and b.x_IsManagement <> 1 and b.ReportDate <= @jzdate group by b.ProjGUID
),--设计本期发生
bqfssj as (select b.ProjGUID,COUNT(*) bqfscs,										--本次发生次数SUM(ISNULL(a.ApproveAmount_Bz,0)) bqfsAmount			--本次发生金额from cb_DesignAlterToContract aleft join cb_DesignAlter b on a.DesignAlterGuid = b.DesignAlterGUIDwhere b.ApproveStatusEnum = 3 and b.x_IsManagement <> 1 and b.ReportDate between @ksdate and @jzdate group by b.ProjGUID
),--设计累计确认
ljqrsj as (select b.ProjGUID,COUNT(*) ljqrcs,										--累计确认次数SUM(ISNULL(c.ValidationAmount_Bz,0)) ljqrAmount			--累计确认金额from cb_DesignAlterToContract ainner join cb_DesignAlter b on a.DesignAlterGuid = b.DesignAlterGUIDleft join cb_DesignAlterZJSP c on a.DesignAlterGuid = c.DesignAlterGUID and a.ContractGUID = c.ContractGUIDwhere b.ApproveStatusEnum = 3 and b.x_IsManagement <> 1 and b.ReportDate <= @jzdate group by b.ProjGUID
)

2.临时表

临时表的数据是需要插入到数据库的，所以一般用于做报表的话，在插入数据之后需要删除临时表。

if OBJECT_ID('tempdb..#f1') is not null 
drop table #f1
SELECT Getin.ProjGUID,Getin.BldGUID,Getin.TopProductTypeGUID,SUM(ISNULL(CurrGetin.CurrHjAmount, 0)) AS F1into #f1 FROM   data_wide_s_Getin  Getin WITH(NOLOCK)LEFT JOIN ( SELECT   GetinGUID,SUM(CASE WHEN ItemType='非贷款类房款'AND IsFk = 1OR ItemNameGUID = '9165FAED-227A-465D-AA5D-D24BED655677' /*银行按揭*/OR ItemNameGUID = 'C3190DC3-C295-4A98-B7AC-9DFF7D7A0091' /*公积金*/ THEN ISNULL(RmbAmount, 0)ELSE 0 END) AS CurrHjAmountFROM     data_wide_s_Getin WITH(NOLOCK)WHERE    --ProjGUID IN (@ProjGuids)--        AND VouchStatus <> '作废'AND CONVERT(VARCHAR(100), SkDate, 23) >= CONVERT(VARCHAR(100), @BeginDate, 23)AND CONVERT(VARCHAR(100), SkDate, 23) <= CONVERT(VARCHAR(100), @EndDate, 23)GROUP BY GetinGUID ) AS CurrGetinON Getin.GetinGUID = CurrGetin.GetinGUIDGROUP BY Getin.ProjGUID,Getin.BldGUID,Getin.TopProductTypeGUID

3.运行效率

用with as ,其实跟直接用子查询效率上没有什么区别；而用临时表与永久表相似，数据是真是跑入到数据库里面去的，相当于第二次直接关联的是一个小表，查询效率大大提高。

4.应用场景

临时表适用于：有很多复杂的关联子表查询。

with as 适用于：为了增加代码可读性，且没有很多复杂的关联子查询。