sql - 使用 SQL 的二进制搜索查询

Question

我在 SQL Server 2014 表中有一个二叉树:

UserID ParentUserID Position 
----------------------------
1      Null         Null     <-- ROOT
2      1            Left
3      1            Right    <-- Last Right for ID=1 (CTE query return 3)
4      2            Left
5      4            Left
6      5            Left
7      6            Left     <-- Last Left for ID=1 (CTE query return 6)

要获取最后一个左边的 ID 和最后一个右边的 ID，我正在使用 CTE 查询:

; with  left_hand_recurse as
(
        select  UserID
        ,       ParentUserID
        ,       1 as depth
        from    Table1 where ParentUserID is null
        union all
        select  child.UserID
        ,       child.ParentUserID
        ,       parent.depth + 1
        from    left_hand_recurse parent
        join    Table1 child
        on      parent.UserID = child.ParentUserID
                and position = 'Left'
)
select  top 1 *
from    left_hand_recurse
order by
        depth desc
;

和

; with  right_hand_recurse as
(
        select  UserID
        ,       ParentUserID
        ,       1 as depth
        from    Table1 where ParentUserID is null
        union all
        select  child.UserID
        ,       child.ParentUserID
        ,       parent.depth + 1
        from    right_hand_recurse  parent
        join    Table1 child
        on      parent.UserID = child.ParentUserID
                and position = 'Right'
)
select  top 1 *
from    right_hand_recurse 
order by
        depth desc
;

它工作正常。这样我就可以在左侧或右侧获取根的最后一个 UserID(对于 ParentUserID == 1)

我需要修改 CTE 查询以获得相同的结果，但对于我想作为参数 @ParentUserID 传递的特定 ParentUserID。

如何实现这个目标？

Answer 1

只需在每个 CTE 中更改这一行:

    from    Table1 where ParentUserID is null

到:

    from    Table1 where ParentUserID = @ParentId