问题描述
我有一个相当复杂的查询,其中包含多个 CTE,但有 1 个主要 CTE,其他人都从中提取,这是否会导致该主要 CTE 被多次执行?
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 的多次执行(实际执行计划见 Number of Executions 属性).
This will prevent multiple executions (for actual execution plan see Number of Executions property) for cteFiveYear.
示例:如果您执行此查询
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 数据库,那么实际的执行计划将是
using AdventureWorks2008R2 database then the actual execution plan will be