萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mssql數據庫 >> 在sqlserver中如何使用CTE解決復雜查詢問題

在sqlserver中如何使用CTE解決復雜查詢問題

最近,同事需要從數個表中查詢用戶的業務和報告數據,寫了一個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 

該查詢需要執行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 語句的執行范圍內定義。該子句也可用在 CREATE VIEW 語句中,作為該語句的 SELECT 定義語句的一部分。公用表表達式可以包括對自身的引用。這種表達式稱為遞歸公用表表達式。

下面看看經過CET改寫過的查詢:

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 '約定書數'
from 
(
select Name, AccountantCode,COUNT( BusinessBackupCustomerId) as '報告數'
from CTE
group by Name,AccountantCode
) t0

執行此查詢,只需要5秒鐘時間,比原來的查詢提高了一倍。

注意上面的Count函數,它統計了一個列,如果該列在某行的值為NULL,將不會統計該行,這正符合需求。

另外,CTE還可以做遞歸處理,詳細見上面的聯機叢書URL的內容說明。

copyright © 萬盛學電腦網 all rights reserved