問題描述

我有一個相當(dāng)復(fù)雜的查詢，其中包含多個 CTE，但有 1 個主要 CTE，其他人都從中提取，這是否會導(dǎo)致該主要 CTE 被多次執(zhí)行?

I have a fairly complicated query with multiple CTEs but 1 main CTE that the others all pull from, does this cause that main CTE to be executed multiple times?

推薦答案

你可以這樣使用 CROSS JOIN:

You could use CROSS JOIN thus:

SELECT 
    AVG(CASE WHEN instructorID = @instructorID THEN score END) InstructorSemesterAverage,
    STDEVP(CASE WHEN instructorID = @instructorID THEN score END) InstructorSemesterSTDeviation,
    AVG(CASE WHEN subjectCode = @subjectCode THEN score END) DepartmentSemesterAverage, 
    STDEVP(CASE WHEN subjectCode = @subjectCode THEN score END) DepartmentSemesterSTDeviation, 
    AVG(CASE WHEN bannerCRN=@CRN AND Q.year = @year AND semester = @semester THEN score END) ClassScore,
    STDEVP(CASE WHEN bannerCRN=@CRN AND Q.year = @year AND semester = @semester THEN score END) ClassSTDeviation,
    (SELECT DecTile FROM cteNtile WHERE instructorID = @instructorID)*10 DecTile,
    X.DepartmentClassFiveYearAverage AS DepartmentClassFiveYearAverage,
    X.DepartmentClassFiveYearSTDeviation AS DepartmentClassFiveYearSTDeviation,
    X.InstructorClassFiveYearAverage AS InstructorClassFiveYearAverage,
    X.InstructorClassFiveYearSTDeviation AS InstructorClassFiveYearSTDeviation
FROM 
    cteMain Q CROSS JOIN cteFiveYear X

這將防止 cteFiveYear 的多次執(zhí)行(實際執(zhí)行計劃見 Number of Executions 屬性).

This will prevent multiple executions (for actual execution plan see Number of Executions property) for cteFiveYear.

示例:如果您執(zhí)行此查詢

Example: If you execute this query

SELECT  h.ProductID,h.StandardCost,
        x.AvgPrice
FROM    Production.ProductCostHistory h
CROSS JOIN (
    SELECT  AVG(p.ListPrice) AvgPrice
    FROM    Production.Product p
) x

使用 AdventureWorks2008R2 數(shù)據(jù)庫，那么實際的執(zhí)行計劃將是

using AdventureWorks2008R2 database then the actual execution plan will be

這篇關(guān)于在同一個查詢中多次調(diào)用 CTE的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！