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 #f1SELECT 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 CurrHjAmountFROMdata_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 适用于：为了增加代码可读性，且没有很多复杂的关联子查询。
