看来,from this MSDN article,我发现的问题是功能：
Analytic and aggregate functions in the recursive part of the CTE are
applied to the set for the current recursion level and not to the set
for the CTE. Functions like ROW_NUMBER operate only on the subset of
data passed to them by the current recursion level and not the entire
set of data pased to the recursive part of the CTE. For more
information,see J. Using analytical functions in a recursive CTE.
在我的挖掘中,我无法找到解释为什么选择这样做是为了工作呢？这是一个基于集合的语言的程序化方法,所以这对我的sql思维过程起反作用,在我看来相当混乱.有没有人知道和/或任何人可以解释为什么递归CTE以递归方式在递归级别处理分析函数？
以下是帮助可视化的代码：
Here is the SQLFiddle for the CTE (only showing the 2nd level of the recursion)
WITH myCTE AS ( SELECT *,ROW_NUMBER() OVER (ORDER BY score desc) AS RowNumber,1 AS RecurseLevel FROM tblGroups WHERE ParentId IS NULL UNION ALL SELECT tblGroups.*,ROW_NUMBER() OVER (ORDER BY myCTE.RowNumber,tblGroups.score desc) AS RowNumber,RecurseLevel + 1 AS RecurseLevel FROM tblGroups JOIN myCTE ON myCTE.GroupID = tblGroups.ParentID ) SELECT * FROM myCTE WHERE RecurseLevel = 2;
WITH myCTE
AS
(
SELECT *,1 AS RecurseLevel
FROM tblGroups
WHERE ParentId IS NULL
)
SELECT tblGroups.*,RecurseLevel + 1 AS RecurseLevel
FROM tblGroups
JOIN myCTE
ON myCTE.GroupID = tblGroups.ParentID;
我总是设想sql递归CTE运行更像this while loop
DECLARE @RecursionLevel INT
SET @RecursionLevel = 0
SELECT *,@RecursionLevel AS recurseLevel
INTO #RecursiveTable
FROM tblGroups
WHERE ParentId IS NULL
WHILE EXISTS( SELECT tblGroups.* FROM tblGroups JOIN #RecursiveTable ON #RecursiveTable.GroupID = tblGroups.ParentID WHERE recurseLevel = @RecursionLevel)
BEGIN
INSERT INTO #RecursiveTable
SELECT tblGroups.*,ROW_NUMBER() OVER (ORDER BY #RecursiveTable.RowNumber,recurseLevel + 1 AS recurseLevel
FROM tblGroups
JOIN #RecursiveTable
ON #RecursiveTable.GroupID = tblGroups.ParentID
WHERE recurseLevel = @RecursionLevel
SET @RecursionLevel = @RecursionLevel + 1
END
SELECT * FROM #RecursiveTable ORDER BY RecurseLevel;
解决方法
它们依赖于以下,前面的或完整的结果集来计算当前值.
也就是说,合并视图从不允许包含分析函数的视图.为什么？
这样会改变结果.
例如：
Select * from (
select row_number() over (partition by c1 order by c2) rw,c3 from t) z
where c3=123
不一样
select row_number() over (partition by c1 order by c2) rw,c3 from t
where c3=123
这两个将为rw返回不同的值.
这就是为什么包含分析函数的子查询将始终在其中完全解决,而不会与其余的合并.
更新
看第二个查询：
WITH myCTE
AS
(
SELECT *,RecurseLevel + 1 AS RecurseLevel
FROM tblGroups
JOIN myCTE
ON myCTE.GroupID = tblGroups.ParentID;
它的工作原理就好像是(相同的执行计划和结果)：
SELECT tblGroups.*,RecurseLevel + 1 AS RecurseLevel
FROM tblGroups
JOIN (
SELECT *,1 AS RecurseLevel
FROM tblGroups
WHERE ParentId IS NULL
)myCTE ON myCTE.GroupID = tblGroups.ParentID;
这个需要被分区以重置rownumber.
递归查询在while循环中不起作用,它们不是程序性的.在基础上,它们像递归函数一样工作,但是根据表,查询,索引,它们可以被优化为以一种方式运行.
如果我们遵循在使用分析函数时查看无法合并的概念,并且查看查询1.它只能运行一次,并且在嵌套循环中.
WITH myCTE
AS
( /*Cannot be merged*/
SELECT *,1 AS RecurseLevel,cast(0 as bigint) n
FROM tblGroups
WHERE ParentId IS NULL
UNION ALL
/*Cannot be merged*/
SELECT tblGroups.*,RecurseLevel + 1 AS RecurseLevel,myCTE.RowNumber
FROM tblGroups
JOIN myCTE
ON myCTE.GroupID = tblGroups.ParentID
)
SELECT *
FROM myCTE;
所以第一选择,不能合并第二,也不.运行此查询的唯一方法是在每个级别返回的每个项目的嵌套循环中,因此重置.再次,这不是程序性的问题,而是一个可能的执行计划的问题.
希望这回答你的问题,让我如果不:)
ÿ
