在sqlserver中如何使用CTE解決復(fù)雜查詢問題
最近，同事需要從數(shù)個(gè)表中查詢用戶的業(yè)務(wù)和報(bào)告數(shù)據(jù)，寫了一個(gè)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)用的場(chǎ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
指定臨時(shí)命名的結(jié)果集，這些結(jié)果集稱為公用表表達(dá)式 (CTE)。該表達(dá)式源自簡(jiǎn)單查詢，并且在單條 SELECT、INSERT、UPDATE、MERGE 或 DELETE 語句的執(zhí)行范圍內(nèi)定義。該子句也可用在 CREATE VIEW 語句中，作為該語句的 SELECT 定義語句的一部分。公用表表達(dá)式可以包括對(duì)自身的引用。這種表達(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.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ù)'
from
(
select Name, AccountantCode,COUNT( BusinessBackupCustomerId) as '報(bào)告數(shù)'
from CTE
group by Name,AccountantCode
) t0
執(zhí)行此查詢，只需要5秒鐘時(shí)間，比原來的查詢提高了一倍。
注意上面的Count函數(shù)，它統(tǒng)計(jì)了一個(gè)列，如果該列在某行的值為NULL，將不會(huì)統(tǒng)計(jì)該行，這正符合需求。
另外，CTE還可以做遞歸處理，詳細(xì)見上面的聯(lián)機(jī)叢書URL的內(nèi)容說明。
- sql server使用公用表表達(dá)式CTE通過遞歸方式編寫通用函數(shù)自動(dòng)生成連續(xù)數(shù)字和日期
- SqlServer使用公用表表達(dá)式（CTE）實(shí)現(xiàn)無限級(jí)樹形構(gòu)建
- SQL Server 公用表表達(dá)式(CTE)實(shí)現(xiàn)遞歸的方法
- 使用SqlServer CTE遞歸查詢處理樹、圖和層次結(jié)構(gòu)
- SQL SERVER 2008 CTE生成結(jié)點(diǎn)的FullPath
- SQLSERVER2008中CTE的Split與CLR的性能比較
- 使用SQLSERVER 2005/2008 遞歸CTE查詢樹型結(jié)構(gòu)的方法
- SQL?Server使用T-SQL進(jìn)階之公用表表達(dá)式(CTE)
相關(guān)文章
對(duì)于一些敏感數(shù)據(jù)，如密碼、卡號(hào)，一般不能使用正常數(shù)值來存儲(chǔ)。否則會(huì)有安全隱患。以往的加密解密都有前端應(yīng)用程序來輔助完成。而數(shù)據(jù)庫(kù)一般只能加密不能解密2012-06-06
SQL Server解析XML數(shù)據(jù)的方法詳解
這篇文章主要介紹了SQL Server解析XML數(shù)據(jù)的方法,結(jié)合實(shí)例形式詳細(xì)分析了SQL Server針對(duì)xml數(shù)據(jù)的讀取,遍歷,刪除,查找等常用操作技巧,具有一定參考借鑒價(jià)值,需要的朋友可以參考下2016-06-06
SQL Server全文索引服務(wù)...2006-09-09
世界杯猜想活動(dòng)的各類榜單的SQL語句小結(jié)
自己網(wǎng)站的世界杯猜想活動(dòng)，整理了幾個(gè)排行榜。寫了半個(gè)小時(shí)的SQL，丟了多可惜，放在這里，反正是別人的地盤，不心疼。2010-07-07
Sql檢驗(yàn)數(shù)據(jù)庫(kù)中各個(gè)表的數(shù)據(jù)量
本文主要介紹了Sql檢驗(yàn)數(shù)據(jù)庫(kù)中各個(gè)表的數(shù)據(jù)量,這樣的查詢有助于評(píng)估每個(gè)表的數(shù)據(jù)量,從而更好地進(jìn)行數(shù)據(jù)庫(kù)性能優(yōu)化,具有一定的參考價(jià)值,感興趣的可以了解一下2023-12-12
SqlServer數(shù)據(jù)庫(kù)腳本執(zhí)行命令行指令方式
這篇文章主要介紹了SqlServer數(shù)據(jù)庫(kù)腳本執(zhí)行命令行指令方式，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-06-06
在安裝好SQL?Server后,默認(rèn)sa用戶是禁用且也沒有啟用SQL?Server登陸驗(yàn)證,這需要手動(dòng)開啟,下面這篇文章主要給大家介紹了關(guān)于SQL?Server中啟用SA賬戶的最新詳細(xì)教程,需要的朋友可以參考下2024-04-04
電商業(yè)務(wù)中需要計(jì)算用戶留存情況,在時(shí)間維度上可以分為用戶次日、多日后的留存情況,本文就來詳細(xì)的介紹一下如何計(jì)算,具有一定的參考價(jià)值,感興趣的可以了解一下2024-09-09
DATASET 與 DATAREADER對(duì)象有什么區(qū)別
DataReader和DataSet最大的區(qū)別在于,DataReader使用時(shí)始終占用SqlConnection(俗稱：非斷開式連接),在線操作數(shù)據(jù)庫(kù)時(shí)，任何對(duì)SqlConnection的操作都會(huì)引發(fā)DataReader的異常。下面同本文對(duì)dataset與datareader的區(qū)別詳細(xì)學(xué)習(xí)吧2016-11-11
這篇文章主要介紹了SQL?多表聯(lián)合查詢的幾種方式,連接查詢是關(guān)系型數(shù)據(jù)庫(kù)中的一個(gè)重要標(biāo)志，這次和大家一起復(fù)習(xí)多表查詢的幾種操作，結(jié)合實(shí)例代碼講解的非常詳細(xì)，需要的朋友可以參考下2023-02-02