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 a
	left join cb_DesignAlter b on a.DesignAlterGuid = b.DesignAlterGUID
	where 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 a
	left join cb_DesignAlter b on a.DesignAlterGuid = b.DesignAlterGUID
	where 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 a
	inner join cb_DesignAlter b on a.DesignAlterGuid = b.DesignAlterGUID
	left join cb_DesignAlterZJSP c on a.DesignAlterGuid = c.DesignAlterGUID and a.ContractGUID = c.ContractGUID
	where 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 F1
			into #f1 FROM   data_wide_s_Getin  Getin WITH(NOLOCK)
					LEFT JOIN ( SELECT   GetinGUID,
										SUM(CASE WHEN ItemType='非贷款类房款'
														AND IsFk = 1
														OR ItemNameGUID = '9165FAED-227A-465D-AA5D-D24BED655677' /*银行按揭*/
														OR ItemNameGUID = 'C3190DC3-C295-4A98-B7AC-9DFF7D7A0091' /*公积金*/ THEN ISNULL(RmbAmount, 0)ELSE 0 END) AS CurrHjAmount
								FROM     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 CurrGetin
						ON Getin.GetinGUID = CurrGetin.GetinGUID
			GROUP BY Getin.ProjGUID,Getin.BldGUID,Getin.TopProductTypeGUID

3.运行效率

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

4.应用场景

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

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