在sqlserver中如何使用CTE解决复杂查询问题
最近，同事需要从数个表中查询用户的业务和报告数据，写了一个SQL语句，查询比较慢：
Select S.Name, S.AccountantCode, ( SelectCOUNT(*)from( SelectDistinctBusinessBackupIdfromBiz_BusinessBackupCustomerwhereIdin( SelectBusinessBackupCustomerIdfromRpt_RegistFormwhere(SignatureCPA1Id=S.IdorSignatureCPA2Id=S.Id)andDocStatus=30 ))T )as'BNum', (casewhenR.Idisnullthen0else1end)as'Num', R.ReportBackupDate from Base_StaffS leftjoinRpt_RegistFormRon(R.SignatureCPA1Id=S.IdorR.SignatureCPA2Id=S.Id)andR.DocStatus=30 whereS.UserType=3
该查询需要执行10秒左右，仔细分析，它有2次查询类似的结果集（Base_Staff,Rpt_RegistForm关联部分），这正是CTE应用的场合。
从SQLSERVER联机丛书，我们来了解下CET的概念：
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.zh-CHS/s10de_6tsql/html/27cfb819-3e8d-4274-8bbe-cbbe4d9c2e23.htm
指定临时命名的结果集，这些结果集称为公用表表达式(CTE)。该表达式源自简单查询，并且在单条SELECT、INSERT、UPDATE、MERGE或DELETE语句的执行范围内定义。该子句也可用在CREATEVIEW语句中，作为该语句的SELECT定义语句的一部分。公用表表达式可以包括对自身的引用。这种表达式称为递归公用表表达式。
下面看看经过CET改写过的查询：
WithCTEas ( select --s.IdasS_ID, s.Name,s.AccountantCode, r.BusinessBackupCustomerId--,r.IdasR_ID,r.SignatureCPA1Id,r.SignatureCPA2Id fromBase_StaffS leftjoinRpt_RegistFormR on(R.SignatureCPA1Id=S.IdorR.SignatureCPA2Id=S.Id)andr.DocStatus=30 wheres.UserType=3 ) selectt0.* ,( SelectCOUNT(*)from( SelectDistinctBusinessBackupId fromBiz_BusinessBackupCustomerb innerjoinCTEonb.Id=CTE.BusinessBackupCustomerId wheret0.AccountantCode=CTE.AccountantCode )t1 )as'约定书数' from ( selectName,AccountantCode,COUNT(BusinessBackupCustomerId)as'报告数' fromCTE groupbyName,AccountantCode )t0
执行此查询，只需要5秒钟时间，比原来的查询提高了一倍。
注意上面的Count函数，它统计了一个列，如果该列在某行的值为NULL，将不会统计该行，这正符合需求。
另外，CTE还可以做递归处理，详细见上面的联机丛书URL的内容说明。