我有一个父子关系表，如下所示。我想检索所有记录的父或子ID像所有祖先和父母，如果可能的话，与深度。例如，我想找到D的族，它将返回前14行，因为它们都属于同一族。这样的家庭可能有好几套。我想查询一个成员，想得到整个家庭的记录。是否有可能使用CTE来实现这一点?每个表记录的族结构:
A
/
B C G J
/ / /
M D E H K
/ / /
N F I L
R
|
S U
/
T
请帮助。表是这样的:
Parent Child
------ ------
A B
A C
B D
D F
M F
M N
C E
G E
G H
J H
J K
H I
K I
K L
R S
S T
U T
谢谢,
Himadri
此解决方案仅适用于非循环图，因为如果循环使用所提供的数据集，递归将中断…
create table nodes
(
p char,
c char
)
insert into nodes (p, c)
values
('A', 'B'), ('A', 'C'), ('B', 'D'), ('D', 'F'), ('M', 'F'), ('M', 'N'), ('C', 'E'),
('G', 'E'), ('G', 'H'), ('J', 'H'), ('J', 'K'), ('H', 'I'), ('K', 'I'), ('K', 'L'),
('R', 'S'), ('S', 'T'), ('U', 'T')
GO
我们可以通过对原始节点记录递归得到前向树的父->子
CREATE VIEW dbo.Tree
AS
WITH Hierarchy(r, p, c, [Level])
AS
(
SELECT p AS r,
p,
c,
0 AS [Level]
FROM dbo.nodes
UNION ALL
SELECT n.p AS r,
t.p,
t.c,
t.[Level] + 1
FROM Hierarchy t
INNER JOIN dbo.nodes n ON n.c = t.r
AND n.p != t.p
)
SELECT r, p, c, [Level]
FROM Hierarchy
然后给出结果
r p c Level
A A B 0
A A C 0
A C E 1
A D F 2
A B D 1
B D F 1
B B D 0
C C E 0
D D F 0
G G E 0
G G H 0
G H I 1
H H I 0
J H I 1
J K L 1
J K I 1
J J H 0
J J K 0
K K I 0
K K L 0
M M F 0
M M N 0
R R S 0
R S T 1
S S T 0
U U T 0
我们可以用另一种方式来做，这样我们就可以从子节点向上走到父节点
CREATE VIEW dbo.ReverseTree
AS
WITH Hierarchy(r, c, p, [Level])
AS
(
SELECT c AS r,
c,
p,
0 AS [Level]
FROM dbo.nodes
UNION ALL
SELECT n.c AS r,
t.c,
t.p,
t.[Level] + 1
FROM Hierarchy t
INNER JOIN dbo.nodes n ON n.p = t.r
AND n.c != t.c
)
SELECT r, c, p, [Level]
FROM Hierarchy
结果是这样的
r c p Level
B B A 0
C C A 0
D D B 0
D B A 1
E C A 1
E E C 0
E E G 0
F F D 0
F F M 0
F D B 1
F B A 2
H H G 0
H H J 0
I I H 0
I I K 0
I K J 1
I H J 1
I H G 1
K K J 0
L K J 1
L L K 0
N N M 0
S S R 0
T T S 0
T T U 0
T S R 1
这对于像面包屑路径这样的东西很方便
我找到了一个解决方案。但是我在while循环中使用了CTE。如果有人有任何其他的解决方案，请建议。正如我上面提到的一个包含家庭记录的表格，或者你可以说图表。我们将其命名为tbl_ParentChild。
下面是我的代码:Declare @Child varchar(10), @RowsEffected int
Set @Child='D'-----It is the member whose family we want to find
CREATE Table #PrntChld (Parent varchar(10),Child varchar(10))
Insert Into #PrntChld
Select Parent,Child from tbl_ParentChild MF
Where MF.Child=@Child or MF.Parent=@Child
Select @RowsEffected=Count(*) from #PrntChld
While @RowsEffected>0
BEGIN
;WITH Prnt(Parent,Child)
AS
( Select M.Parent,M.Child from tbl_ParentChild M
Inner Join #PrntChld F On F.Child=M.Child
UNION ALL
SELECT e.Parent,e.Child
FROM tbl_ParentChild AS E
INNER JOIN Prnt AS M
ON E.Child = M.Parent
),
PrntChld(Parent,Child)
AS
( Select M.Parent,M.Child from tbl_ParentChild M
Inner Join (Select * from Prnt union Select * from #PrntChld) F On M.Parent=F.Parent
UNION ALL
SELECT e.Parent,e.Child
FROM tbl_ParentChild AS E
INNER JOIN PrntChld AS M
ON M.Child = E.Parent
)
Insert Into #PrntChld
Select distinct MF.* from PrntChld MF
Left Join #PrntChld T On T.Child =MF.Child and T.Parent = MF.Parent
where T.Child is null
Select @RowsEffected=@@ROWCOUNT
END
Select * from #PrntChld
drop table #PrntChld
我将用递归的字符串限制器发布我的解决方案。我想知道对于一个大图表来说，这样做是否对性能有好处，但我认为这并不是那么糟糕。基本上，我正在递归地处理关系，记录每一步的"路径"，并检查路径，以便我可以在遇到循环关系时停止。
对于任何想要尝试它的人，我发布了表的创建和填充:
create table nodes
(
p char,
c char
)
insert into nodes (p, c)
values
('A', 'B'), ('A', 'C'), ('B', 'D'), ('D', 'F'), ('M', 'F'), ('M', 'N'), ('C', 'E'),
('G', 'E'), ('G', 'H'), ('J', 'H'), ('J', 'K'), ('H', 'I'), ('K', 'I'), ('K', 'L'),
('R', 'S'), ('S', 'T'), ('U', 'T')
下面是查询:
declare @let char = 'D';
with cte as
(
--get the node itself if it exists in the table at all
select
top 1 @let as letter, '' as rec_path
from
nodes
where
c = @let or p = @let
union all
-- get the direct relations of the node as a starting point
select
case when c = @let then p else c end as letter,
cast(@let as varchar(max)) as rec_path
from
nodes
where
c = @let or p = @let
union all
-- get all of the relations recursively until you reach a node you already processed
select
case when c = cte.letter then p else c end as letter,
rec_path + cte.letter as rec_path
from
cte
join nodes on
(cte.letter = nodes.c and charindex(cast(nodes.p as varchar(1)), rec_path, 1) = 0
or (cte.letter = nodes.p and charindex(cast(nodes.c as varchar(1)), rec_path, 1) = 0))
)
select
distinct letter
from
cte