看来,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以递归方式在递归级别处理分析函数？

以下是帮助可视化的代码：

注意,每个代码输出中的RowNumber列.

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;

Here is the second SQLFiddle for what I would expect the CTE to do (again only need the 2nd level to display the issue)

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;