Server 2008 -获取底部链接最多的记录

Question

我的Server数据库中有以下数据：

Id | Name | LinkedId
---+------+----------
1  | A    | 1
2  | B    | 2
3  | C    | 1
4  | D    | 3
5  | E    | 4

现在，我想编写一个存储过程，其中应该显示以下记录：

注意事项：LinkedId具有与该名称相关联的Id。

例如，：："C“与"A”相关联

Id | Name | LinkedId
---+------+---------
1  | A    | 1
2  | B    | 2
3  | C    | 1
4  | D    | 1 //here instead of showing 3, it showed 1 which is the bottom most value in the tree
5  | E    | 1 //same case as the above

问题

对于这种情况，根据我有限的知识，我只能考虑使用联接(左，内)，但在这种情况下，这还不足以获得最底层的链接id。

编辑(输出)：

我希望所有与"C“项直接或间接关联的项目

Id | Name | 
---+------+
3  | C    | 
4  | D    | 
5  | E    | 

Answer 1

您可以使用递归函数。

使用CTE的一个简单的解释递归函数是一个用于计算的common table expression。它包括：

1. 调用例程。

递归CTE的第一次调用包括一个或多个CTE_query_definitions，由UNION、UNION、of或INTERSECT加入。

由于这些查询定义构成CTE结构的基本结果集，因此它们被称为锚成员。

CTE_query_definitions被认为是锚点成员，除非它们引用CTE本身。

所有锚成员查询定义必须位于第一个递归成员定义之前，并且必须使用UNION操作符将最后一个锚成员与第一个递归成员连接起来。

1. 例程的递归调用。

递归调用包括一个或多个CTE_query_definitions，由引用CTE本身的UNION操作符加入。这些查询定义称为递归成员。

1. 终止支票。

终止检查是隐式的；当上次调用没有返回任何行时，递归将停止。

参考链接：使用CTE的递归查询

递归CTE的简单实例 Cte sql服务器

DECLARE @SampleData AS TABLE (Id int, Name varchar(10), LinkedId int)

INSERT INTO @SampleData 
VALUES (1, 'A', 1), (2, 'B', 2), 
(3, 'C', 1),(4, 'D', 3),(5, 'A', 4)

;WITH temp AS 
(
   SELECT sd.Id, sd.Name,  sd.Id AS RootId 
   FROM @SampleData sd WHERE sd.LinkedId = sd.Id -- Invocation of the routine, in this case it's root node of tree.

   UNION ALL
   -- Recursive invocation of the routine
   SELECT sd.Id, sd.Name,  t.RootId AS RootId  
   FROM temp t
   INNER JOIN @SampleData sd ON sd.LinkedId = t.Id AND sd.LinkedId <> sd.Id
   -- Termination check: sd.LinkedId = t.Id AND sd.LinkedId <> sd.Id. 
   -- It make recursive query is not an infinitive loop
)
SELECT  t.Id, t.Name, t.RootId AS LinkedId
FROM temp t
OPTION (MAXRECURSION 0) -- this option remove recursive max depth, default is 100.

演示链接：雷克斯试验器

对于新输出，可以更改The first invocation of the recursive CTE

;WITH temp AS 
(
   SELECT sd.Id, sd.Name,  sd.Id AS RootId 
   FROM @SampleData sd WHERE sd.Id = 3

   UNION ALL
   -- Recursive invocation of the routine
   SELECT sd.Id, sd.Name,  t.RootId AS RootId  
   FROM temp t
   INNER JOIN @SampleData sd ON sd.LinkedId = t.Id AND sd.LinkedId <> sd.Id
   -- Termination check: sd.LinkedId = t.Id AND sd.LinkedId <> sd.Id. 
   -- It make recursive query is not an infinitive loop
)
SELECT  t.Id, t.Name, t.RootId AS LinkedId
FROM temp t
OPTION (MAXRECURSION 0) -- this option remove recursive max depth, default is 100.

Answer 2

您需要一个递归过程：

with cte as (
      select t.id, t.name, t.linkedid, 1 as lev
      from t
      where t.linkedid = t.id
      union all
      select t.id, t.name, cte.linkedid, cte.lev + 1
      from t join
           cte
           on cte.id = t.linkedid and t.linkedid <> t.id
     )
select id, name, linkedid
from cte;

这里是在实践中起作用的。