将所有孩子的父母包括在内和排除在一棵树上

Question

我有一个类似下面简化版本的查询，它生成下面的结果表。

在这种情况下，数据由两个树结构组成，每个树结构有三个级别。

对于每棵树("根"(，我需要的是每一个孩子，以及所有包含它的父母和所有排除它的父母。

查询可以工作，但由于使用了交叉应用方法，执行时间非常慢。

还有其他更快的方法吗？

DECLARE @Data TABLE (Rt VARCHAR(MAX), Parent VARCHAR(MAX), Child VARCHAR(MAX))
INSERT INTO @Data (Rt, Parent, Child) VALUES
('root1', 'parent1', 'child1'),
('root1', 'parent1', 'child2'),
('root1', 'parent2', 'child1'),
('root1', 'parent2', 'child2'),
('root1', 'parent2', 'child3'),
('root1', 'parent3', 'child2'),
('root1', 'parent3', 'child3'),
('root1', 'parent4', 'child4'),
('root1', 'parent5', 'child2'),
('root2', 'parent6', 'child1'),
('root2', 'parent7', 'child5')
SELECT 
D.Rt 'Root'
, D.Child
, STRING_AGG(D.Parent, ', ') 'In-Parents'
, STRING_AGG(xP.Parent, ', ') 'Ex-Parents'
FROM @Data D
CROSS APPLY (
SELECT
Di.Parent
FROM @Data Di
WHERE Di.Rt = D.Rt
EXCEPT
SELECT
Dii.Parent
FROM @Data Dii
WHERE
Dii.Rt = D.Rt
AND Dii.Child = D.Child
) AS xP
GROUP BY
D.Rt
, D.Child

在父母父项1、父项2parent1、parent2、parent3、parent5父项6[/tr>父项7

根		子	前父母
root1	子项1	父级3、父级4、父级5
	child2	parent1、parent2、parent3、parent5	parent4
	child3	parent2，parent3	parent1，parent4，parent5
	child4	parent4
root2	子项1	父级7
	子项5	父级6

我会先尝试生成所有父子组合，然后测试它们是否存在于数据中。例如：

select
p.root, 
c.child,
string_agg(d.parent, ', ') as in_parents,
string_agg(case when d.parent is null then p.parent end, ', ') as ex_parents  
from (select distinct root, parent from data) p
join (select distinct root, child from data) c on c.root = p.root
left join data d on d.root = p.root and d.parent = p.parent 
and d.root = c.root and d.child = c.child
group by p.root, c.child
order by p.root, c.child

结果：

root   child   in_parents                          ex_parents                         
------ ------- ----------------------------------- ---------------------------------- 
root1  child1  parent1, parent2                    parent3, parent4, parent5          
root1  child2  parent1, parent2, parent3, parent5  parent4                            
root1  child3  parent2, parent3                    parent1, parent4, parent5          
root1  child4  parent4                             parent1, parent2, parent3, parent5 
root2  child1  parent6                             parent7                            
root2  child5  parent7                             parent6                            

请参阅db<gt；不停摆弄

现在，如果您不需要VARCHAR(MAX)，而是需要VARCHAR(100)之类的功能，那么您可以尝试使用以下形式的索引来加快查询速度：

create index ix1 on data (root, parent, child);

如果查询仍然很慢，请发布执行计划。