我正在使用Microsoft SQL Server 2008,并尝试使用递归查询来完成与本帖子非常相似的事情:T / SQL中的递归子/父查询
自然,我试图简化事情。希望我已经清楚地做到了。我的桌子看起来像这样:
Parent Child
A A
A B
B D
D H
D I
A C
C E
E C
E J
E K
C F
C G
在视觉上,关系看起来像这样:
A
B
D
H
I
C
E
C
J
K
F
G
我需要在表中创建一个新列,该列代表子实体到顶层的路径(在本例中为A):
Parent Child Chain
A A A
A B A,B
B D A,B,D
D H A,B,D,H
D I A,B,D,I
A C A,C
C E A,C,E
E C A,C,E,C
E J A,C,E,J
E K A,C,E,K
C F A,C,F
C G A,C,G
实体C既是实体E的父代又是子代,可以看出其中的皱纹以及此处可能不使用“父子”术语的原因,这会导致无休止的递归循环。
我的想法是在递归循环中更新链值,并将递归调用限制为链列中值为NULL的实体。想法是仅在第一次遇到实体时递归它。我无法使用的内容,并且不确定如何将更新集成到递归调用中:
WITH r(parent,child,mychain)
AS
(
SELECT parent, child, child AS myChain
FROM myTable
WHERE parent = 'A' AND
parent <> child AND
chain IS NULL
UNION ALL
SELECT v.parent, v.child, myChain + ',' + v.child AS myChain
FROM myTable v
INNER JOIN r ON r.child = v.parent
WHERE v.parent <> v.child
)
UPDATE myTable
SET chain = r.myChain
FROM myTable AS c
JOIN r ON c.ID = r.ID
关于如何实现此目标的任何建议?
提前致谢。
编辑:
可能是我简化了一点。因为父级和子级列的实际值很长(大约20个字符以上),所以我真的需要使用记录id来创建链。我修改了西蒙的建议,如下所示。这使我非常接近。但是,C-> E记录不会得到输出(所有记录最终都应该使用链更新)。没关系;我仍在尝试解决。
with r as
(
SELECT id, parent, child,
CAST( id AS VARCHAR(1024) ) AS CHAIN,
0 as level
FROM myTable
WHERE id = '1'
UNION ALL
SELECT c.id, c.parent, c.child,
cast( (r.CHAIN + ',' + CAST( c.id AS varchar(10)) ) AS varchar(1024)) AS CHAIN,
r.level + 1 as level
FROM myTable c
JOIN r
ON r.child = c.parent
WHERE c.parent != c.child AND r.parent != c.child
)
select * from r order by r.level, r.parent, r.child;
尝试这个:
with r as
(
SELECT parent, child,
CAST( (parent + ',' + child) AS VARCHAR(10)) AS chain,
0 as level
FROM myTable
WHERE parent = 'A'
AND parent != child
UNION ALL
SELECT c.parent, c.child,
cast((r.chain + ',' + c.child) as varchar(10)) AS chain,
r.level + 1 as level
FROM myTable c
JOIN r
ON r.child = c.parent
WHERE r.chain NOT LIKE '%,' + c.child + ',%'
)
select *
from r
order by r.level, r.parent, r.child;
检出:SQL Fiddle
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句