sql – 为什么递归CTE在程序上运行分析函数(ROW_NUMBER)？

看来,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;

解决方法

例如：

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;

所以第一选择,不能合并第二,也不.运行此查询的唯一方法是在每个级别返回的每个项目的嵌套循环中,因此重置.再次,这不是程序性的问题,而是一个可能的执行计划的问题.

希望这回答你的问题,让我如果不:)

?

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!