SQL Server 判断 ids 层次 struct 中的整数 (id)

如何判断给定的ID是否在ID的层次 struct 中？例如，假设我在一个名为Locations的自引用表中具有以下层次 struct (例如，这些是层次 struct 位置):

-- Declare the locations table
DECLARE @location_tbl TABLE(
    [location_id] int NULL,
    [parent_id] int NULL,
    [location_name] nvarchar(255) NOT NULL,
    [location_order] int NULL
)
-- Insert dummy data into locations table
INSERT INTO @location_tbl([location_id], [parent_id], [location_name], [location_order])
VALUES
(1, NULL, 'Location 1', 1),
(2, 1, 'Location 2', 2),
(3, 1, 'Location 3', 1),
(4, 1, 'Location 4', 3),
(5, 2, 'Location 5', 1),
(6, 5, 'Location 6', 1),
(7, 2, 'Location 7', 2),
(8, 7, 'Location 8', 1),
(9, 3, 'Location 9', 1),
(10, 9, 'Location 10', 1),
(11, 4, 'Location 11', 2),
(12, 4, 'Location 12', 1)

-- Show the locations table
SELECT * FROM @location_tbl;

-- Show how to get the hierarchy from the locations table, generally defined as a TBF
DECLARE @tree TABLE(
    [location_id] int, 
    [parent_id] int, 
    [level] int, 
    [location_order] int, 
    [location_name] nvarchar(255), 
    [location_name_hierarchy] nvarchar(255), 
    [row_number_hierarchy] nvarchar(255), 
    [location_id_hierarchy] nvarchar(255),
    [hierarchy_id] hierarchyid
);

WITH tree ([location_id], [parent_id], [level], [location_order], [location_name], [location_name_hierarchy], [row_number_hierarchy], [location_id_hierarchy]) AS
(
    SELECT A.[location_id], A.[parent_id], 0 AS [level], A.[location_order], A.[location_name],
        convert(varchar(max),A.[location_name]) AS [location_name_hierarchy],
        convert(varchar(max),right(row_number() over (order by A.[location_order], A.[location_id]),10)) AS [row_number_hierarchy],
        convert(varchar(max),A.[location_id]) AS [location_id_hierarchy]
    FROM @location_tbl AS A
    WHERE A.[parent_id] IS NULL

    UNION ALL

    SELECT B.[location_id], B.[parent_id], tree.[level] + 1, B.[location_order], B.[location_name],
        [location_name_hierarchy] + '/' + convert(varchar(max),B.[location_name]),
        [row_number_hierarchy] + '/' + convert(varchar(max),right(row_number() over (order by B.[location_order], tree.[location_id]),10)),
        [location_id_hierarchy] + '/' + convert(varchar(max),B.[location_id])
    FROM @location_tbl AS B 
    INNER JOIN tree ON tree.[location_id] = B.[parent_id]
)
INSERT INTO @tree([location_id], [parent_id], [level], [location_order], [location_name], [location_name_hierarchy], [row_number_hierarchy], [location_id_hierarchy], [hierarchy_id])
SELECT [location_id], [parent_id], [level], [location_order], [location_name], [location_name_hierarchy], [row_number_hierarchy], [location_id_hierarchy], 
cast('/' + [row_number_hierarchy] + '/' as hierarchyid) AS [hierarchy_id]
FROM tree

-- Location ID of interest
DECLARE @location_id int = 2
-- Show the tree
SELECT * FROM @tree

-- Filter the try by hierarchy containing Location ID of interest...how to do this properly?
SELECT * FROM @tree
WHERE [location_id] = @location_id OR [location_id_hierarchy] LIKE '%' + CAST(@location_id as nvarchar(255)) + '%'

我想筛选树，其中[Location_id_Hierarchy]包含感兴趣的位置id(在本例中为2).

我try 了什么(以及我的 idea ):

我正在使用的查询，

-- Filter the try by hierarchy containing Location ID of interest...how to do this properly?
SELECT * FROM @tree
WHERE [location_id] = @location_id OR [location_id_hierarchy] LIKE '%' + CAST(@location_id as nvarchar(255)) + '%'

不起作用，因为它还返回不在层次 struct 中的行(它包含12行，而不是2行，它将其与2混淆).我正在努力避免再做一次会影响性能的CTE或循环，但我知道这是一种可能的解决方案.有什么我可以做的吗？

WHERE @location_id IN SPLIT([location_id_hierarchy], '/')

？我知道最后那个WHERE子句是完全错误的，但我在想，一定有什么非常简单和有效的方法可以做到这一点，但我看不到.这可能是一个正则表达式类型的问题.有什么主意吗？