您需要使用递归CTE来实现:
DECLARE @t TABLE (ID INT, TO_ID INT, TYPE VARCHAR(100));
INSERT INTO @t VALUES
(123, 103, 'group'),
(176, 103, 'field'),
(256, 169, 'group'),
(103, 234, 'organization'),
(234, 390, 'site'),
(390, 999, 'notme');
DECLARE @start INT = 123;
DECLARE @stop VARCHAR(100) = 'site';
WITH cte AS (
SELECT base.*, 1 AS LVL
FROM @t base
WHERE ID = @start
UNION ALL
SELECT curr.*, LVL + 1
FROM @t curr
INNER JOIN cte prev ON curr.ID = prev.TO_ID
WHERE prev.TYPE <> @stop
)
SELECT *
FROM cte
ORDER BY LVL
递归CTE实际上是一个迭代查询。你从一些行(123行)开始,然后不断将行添加到前一次迭代的结果中,直到满足某些条件(没有行可用或在前一次迭代中找到了site
)。以下是结果:
ID | TO_ID | TYPE | LVL
123 | 103 | group | 1
103 | 234 | organization | 2
234 | 390 | site | 3
如果您不想查找两个节点之间的完整路径,则从curr中删除where子句,并在最后添加WHERE TYPE = site
。