时间戳

首页 > 解决方案 > 按递归深度有限的前任排序

sql - 按递归深度有限的前任排序

问题描述

我有一个这样的链接列表

 LineID      PredecessorLineID SuccessorLineID 
 ----------- ----------------- --------------- 
 3           NULL              2               
 2           3                 815             
 815         2                 1               
 1           815               195             
 195         1                 52

我需要以一种没有 Predecessor 的行首先出现的方式进行排序，然后是第一行作为前任的行，依此类推

我可以用这个查询对其进行排序

 WITH    q ([LineID], PredecessorLineID,  SuccessorLineID, init, lvl) AS
                        (
                            SELECT  [LineID], [PredecessorLineID],  [SuccessorLineID], [LineID], 1
                            FROM    Lines
                            WHERE   [PredecessorLineID] is null 

                            UNION ALL

                            SELECT   mt.[LineID], mt.[PredecessorLineID],  mt.[SuccessorLineID], q.init, q.lvl + 1
                            FROM    q
                            JOIN    Lines mt
                            ON      mt.[LineID] = q.SuccessorLineID                                 

                            )

    select  * from  q Option ( maxrecursion 4  )

这给出了正确的结果，但问题是递归深度。它实际上是 n - 1。我有几千行，需要将 maxrecursion 设置为一个非常高的值。

这个查询需要在一个视图中，这就是为什么我什至不能设置 maxrecursion 并且需要将它设置为服务器的默认值。

有没有办法用更少的递归来做到这一点？

标签： sqlsql-servercommon-table-expression

解决方案

我不建议在 SQL 数据库中以这种方式存储链表。它们处理起来相当麻烦。我的意思是，层次结构很好，但它们通常不超过 100 的深度。“一次性”递归 CTE 可以无限递归。

也就是说，您可能有一个有效的用例。您可以创建 UDF 并设置递归：

create function udf_get_hierarchy ()
returns @q as table (
    . . .  -- column definitions go here
)
BEGIN
    WITH q ([LineID], PredecessorLineID,  SuccessorLineID, init, lvl) AS (
          SELECT  [LineID], [PredecessorLineID],  [SuccessorLineID], [LineID], 1
          FROM Lines
          WHERE [PredecessorLineID] is null 
          UNION ALL
          SELECT mt.[LineID], mt.[PredecessorLineID],  mt.[SuccessorLineID], q.init, q.lvl + 1
          FROM q JOIN
               Lines mt
               ON mt.[LineID] = q.SuccessorLineID                                 
        )
    SELECT * 
    INTO @q
    FROM q
    OPTION ( MAXRECURSION 0 );
END;

如果您正在创建一个函数，您可能希望将一个参数传递给它，以获取特定 id 的层次结构。

推荐阅读