c# - 如何跟踪表行之间的 ID/ParentID 关系？

Question

我有一个类似 records(ID, ParentID) 的表，其中包含以下数据:

ID   ParentID  
1    null  
2    1  
3    2   
4    2  
5    3  
6    null  
7    6  

如果将此表绘制成一个家族，1,2,3,4,5 将相互关联。

我想找到一种方法来传递一个 ID(如 3)，这样它就可以为我提供其他家庭成员。我正在使用 C# 和 SQL，两者都可以 - 我想找到这样的结果:

3 - result 1,2,4,5
2 - result 1,3,4,5
6 - result 7
and so on

我想找到我传入的 ID 的父级，即祖 parent 、子女和孙子女(如我的示例)。

Answer 1

应该这样做。

CREATE TABLE #Test
(
    ID int,
    ParentID int
)

INSERT #Test VALUES (1, null)
INSERT #Test VALUES (2, 1)
INSERT #Test VALUES (3, 2)
INSERT #Test VALUES (4, 2)
INSERT #Test VALUES (5, 3)
INSERT #Test VALUES (6, null)
INSERT #Test VALUES (7, 6)

DECLARE @QueryId int
SET @QueryId = 2

-- My parents
SELECT [ParentID] FROM #Test WHERE [ID] = @QueryID AND [ParentID] IS NOT NULL
UNION -- My parent's parents
SELECT [ParentID] FROM #Test WHERE [ID] IN (SELECT [ParentID] FROM #Test WHERE [ID] = @QueryID) AND [ParentID] IS NOT NULL
UNION -- My parent's children (i.e. my siblings), excluding me
SELECT [ID] FROM #Test WHERE [ParentID] IN (SELECT [ParentID] FROM #Test WHERE [ID] = @QueryID) AND [ID] != @QueryID
UNION -- My chidren
SELECT [ID] FROM #Test WHERE [ParentID] = @QueryId
UNION -- My chidren's children
SELECT [ID] FROM #Test WHERE [ParentID] IN (SELECT [ID] FROM #Test WHERE [ParentID] = @QueryId)

DROP TABLE #Test