最近，同事需要從數(shù)個表中查詢用戶的業(yè)務(wù)和報告數(shù)據(jù)，寫了一個SQL語句，查詢比較慢：

Select S.Name,S.AccountantCode,(Select COUNT(*) from (Select Distinct BusinessBackupId from Biz_BusinessBackupCustomer where Id in (Select BusinessBackupCustomerId from Rpt_RegistForm where ( SignatureCPA1Id=S.Id or SignatureCPA2Id=S.Id ) and DocStatus=30 ) ) T ) as 'BNum',(case when R.Id is null then 0 else 1 end ) as 'Num', R.ReportBackupDate from Base_Staff S left join Rpt_RegistForm R on ( R.SignatureCPA1Id=S.Id or R.SignatureCPA2Id=S.Id ) and R.DocStatus=30 where S.UserType=3

該查詢需要執(zhí)行10秒左右，仔細(xì)分析，它有2次查詢類似的結(jié)果集（Base_Staff,Rpt_RegistForm 關(guān)聯(lián)部分），這正是CTE應(yīng)用的場合。

從SQLSERVER 聯(lián)機(jī)叢書，我們來了解下CET的概念：

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.zh-CHS/s10de_6tsql/html/27cfb819-3e8d-4274-8bbe-cbbe4d9c2e23.htm

指定臨時命名的結(jié)果集，這些結(jié)果集稱為公用表表達(dá)式 (CTE)。該表達(dá)式源自簡單查詢，并且在單條 SELECT、INSERT、UPDATE、MERGE 或 DELETE 語句的執(zhí)行范圍內(nèi)定義。該子句也可用在 CREATE VIEW 語句中，作為該語句的 SELECT 定義語句的一部分。公用表表達(dá)式可以包括對自身的引用。這種表達(dá)式稱為遞歸公用表表達(dá)式。

下面看看經(jīng)過CET改寫過的查詢：

With CTE as(select--s.Id as S_ID,s.Name ,s.AccountantCode,r.BusinessBackupCustomerId --, r.Id as R_ID ,r.SignatureCPA1Id,r.SignatureCPA2Idfrom Base_Staff S left join Rpt_RegistForm Ron ( R.SignatureCPA1Id=S.Id or R.SignatureCPA2Id=S.Id ) and r.DocStatus=30 where s.UserType=3 )select t0.*,( Select COUNT(*) from ( Select Distinct BusinessBackupIdfrom Biz_BusinessBackupCustomer b inner join CTE on b.Id =CTE.BusinessBackupCustomerId where t0.AccountantCode=CTE.AccountantCode) t1) as '約定書數(shù)'from (select Name, AccountantCode,COUNT( BusinessBackupCustomerId) as '報告數(shù)'from CTEgroup by Name,AccountantCode) t0

執(zhí)行此查詢，只需要5秒鐘時間，比原來的查詢提高了一倍。

注意上面的Count函數(shù)，它統(tǒng)計了一個列，如果該列在某行的值為NULL，將不會統(tǒng)計該行，這正符合需求。

另外，CTE還可以做遞歸處理，詳細(xì)見上面的聯(lián)機(jī)叢書URL的內(nèi)容說明。