none
不用存储过程,怎么使用with语句对带有邻接结构的表,根据一个int类型的常量限制递归查询级别次数? RRS feed

  • 问题

  • WITH cte
    AS
    (SELECT id FROM table WHERE id in(?,?,?……)
     UNION ALL 
     SELECT p.id  FROM table p
      INNER JOIN cte t ON p.ParentId = t.id 
    )
    WITH  cte AS(
        SELECT id,pId,name FROM table WHERE id=?
        UNION ALL
        SELECT a.id,a.pid,a.name FROM table AS a INNER JOIN 
            cte ON a.id=cte.pId   
    )
    SELECT *  FROM cte


    如上,第一个是自上而下的,第二个是自下而上的,这两种在哪个位置加一个什么表达式或语句约束的常量可以限制递归查询级别次数?



    • 已编辑 Trian555 2018年11月21日 9:31
    2018年11月21日 9:06

答案

  • SELECT *  FROM cte

    OPTION (MAXRECURSION 2);

    2018年11月21日 9:57
  • Hi Trian555,

    这里可以用OPTION (MAXRECURSION 2);来做限制,但是会出现一个问题,当达到这个限制之后,查询回报错。

    The maximum recursion 2 has been exhausted before statement completion

    如果不想看到这个错误信息,我们也可以用下面这个方法:

    WITH  cte AS(
        SELECT id,pId,name, 0 as [Level]
        FROM table WHERE id=?
        UNION ALL
        SELECT a.id,a.pid,a.name,[Level] + 1
        FROM table AS a 
        INNER JOIN cte 
             ON a.id=cte.pId   
        WHERE [Level] < 2
    )
    SELECT *  FROM cte

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • 已标记为答案 Trian555 2018年11月22日 5:41
    2018年11月22日 1:28
    版主

全部回复

  • SELECT *  FROM cte

    OPTION (MAXRECURSION 2);

    2018年11月21日 9:57
  • Hi Trian555,

    这里可以用OPTION (MAXRECURSION 2);来做限制,但是会出现一个问题,当达到这个限制之后,查询回报错。

    The maximum recursion 2 has been exhausted before statement completion

    如果不想看到这个错误信息,我们也可以用下面这个方法:

    WITH  cte AS(
        SELECT id,pId,name, 0 as [Level]
        FROM table WHERE id=?
        UNION ALL
        SELECT a.id,a.pid,a.name,[Level] + 1
        FROM table AS a 
        INNER JOIN cte 
             ON a.id=cte.pId   
        WHERE [Level] < 2
    )
    SELECT *  FROM cte

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • 已标记为答案 Trian555 2018年11月22日 5:41
    2018年11月22日 1:28
    版主