SQL Server：如何检查指定ID是否存在于ID层级结构中

现有存储层级化位置数据的自引用表locations，已通过CTE生成包含层级信息的tree表，其中location_id_hierarchy字段记录ID的层级路径（格式如1/2/5）。需要筛选出层级中包含指定ID（示例为2）的记录，但直接使用LIKE语句会误匹配包含类似12的非层级相关记录，希望找到无需额外CTE或循环的高效实现方式。

-- 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 tree 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)) + '%'

方法1：补充分隔符实现精准字符串匹配 #

给location_id_hierarchy的前后都添加路径分隔符/，再匹配包含/{目标ID}/的格式，彻底避免部分ID的误匹配：

-- Location ID of interest
DECLARE @location_id int = 2

SELECT * FROM @tree
WHERE location_id = @location_id 
   OR CONCAT('/', location_id_hierarchy, '/') LIKE '%/' + CAST(@location_id AS nvarchar(255)) + '/%'

这种方法无需修改现有表结构，仅通过简单的字符串拼接即可实现精准匹配。

方法2：利用hierarchyid内置类型（性能更优） #

tree表中已存在hierarchy_id字段（SQL Server专为层级数据设计的类型），可以借助其IsDescendantOf方法直接筛选目标节点的所有后代（包含节点自身）：

-- Location ID of interest
DECLARE @location_id int = 2
DECLARE @target_hierarchy hierarchyid

-- 获取目标ID对应的hierarchyid
SELECT @target_hierarchy = hierarchy_id FROM @tree WHERE location_id = @location_id

-- 筛选所有属于该节点后代的记录（包含自身）
SELECT * FROM @tree
WHERE hierarchy_id.IsDescendantOf(@target_hierarchy) = 1

这种方法性能远高于字符串匹配，因为hierarchyid类型支持索引优化，适合大规模层级数据的查询场景。

内容的提问来源于stack exchange，提问作者rlarson