最近，同事需要從數(shù)個(gè)表中查詢(xún)用戶(hù)的業(yè)務(wù)和報(bào)告數(shù)據(jù)，寫(xiě)了一個(gè)SQL語(yǔ)句，查詢(xún)比較慢：
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
該查詢(xún)需要執(zhí)行10秒左右，仔細(xì)分析，它有2次查詢(xún)類(lèi)似的結(jié)果集（Base_Staff,Rpt_RegistForm 關(guān)聯(lián)部分），這正是CTE應(yīng)用的場(chǎng)合。
從SQLSERVER 聯(lián)機(jī)叢書(shū)，我們來(lái)了解下CET的概念：
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.zh-CHS/s10de_6tsql/html/27cfb819-3e8d-4274-8bbe-cbbe4d9c2e23.htm
指定臨時(shí)命名的結(jié)果集，這些結(jié)果集稱(chēng)為公用表表達(dá)式 (CTE)。該表達(dá)式源自簡(jiǎn)單查詢(xún)，并且在單條 SELECT、INSERT、UPDATE、MERGE 或 DELETE 語(yǔ)句的執(zhí)行范圍內(nèi)定義。該子句也可用在 CREATE VIEW 語(yǔ)句中，作為該語(yǔ)句的 SELECT 定義語(yǔ)句的一部分。公用表表達(dá)式可以包括對(duì)自身的引用。這種表達(dá)式稱(chēng)為遞歸公用表表達(dá)式。
下面看看經(jīng)過(guò)CET改寫(xiě)過(guò)的查詢(xún)：
With CTE as
(
select
--s.Id as S_ID,
s.Name ,s.AccountantCode,
r.BusinessBackupCustomerId --, r.Id as R_ID ,r.SignatureCPA1Id,r.SignatureCPA2Id
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
)
select t0.*
,(
Select COUNT(*) from (
Select Distinct BusinessBackupId
from Biz_BusinessBackupCustomer b
inner join CTE on b.Id =CTE.BusinessBackupCustomerId
where t0.AccountantCode=CTE.AccountantCode
) t1
) as '約定書(shū)數(shù)'
from
(
select Name, AccountantCode,COUNT( BusinessBackupCustomerId) as '報(bào)告數(shù)'
from CTE
group by Name,AccountantCode
) t0
執(zhí)行此查詢(xún)，只需要5秒鐘時(shí)間，比原來(lái)的查詢(xún)提高了一倍。
注意上面的Count函數(shù)，它統(tǒng)計(jì)了一個(gè)列，如果該列在某行的值為NULL，將不會(huì)統(tǒng)計(jì)該行，這正符合需求。
另外，CTE還可以做遞歸處理，詳細(xì)見(jiàn)上面的聯(lián)機(jī)叢書(shū)URL的內(nèi)容說(shuō)明。
您可能感興趣的文章:- 使用SqlServer CTE遞歸查詢(xún)處理樹(shù)、圖和層次結(jié)構(gòu)
- SQLSERVER2008中CTE的Split與CLR的性能比較
- 使用SQLSERVER 2005/2008 遞歸CTE查詢(xún)樹(shù)型結(jié)構(gòu)的方法
- SQLSERVER2005 中樹(shù)形數(shù)據(jù)的遞歸查詢(xún)
- sqlserver另類(lèi)非遞歸的無(wú)限級(jí)分類(lèi)(存儲(chǔ)過(guò)程版)
- SqlServer使用公用表表達(dá)式（CTE）實(shí)現(xiàn)無(wú)限級(jí)樹(shù)形構(gòu)建
