SQL服务器跳过重复的关系(父子)在递归
我有一棵树,在树中特定的节点可以出现在树中的另一个节点。 (在我的例子中为2):SQL服务器跳过重复的关系(父子)在递归
1
/ \
2 3
/ \ \
4 5 6
\
2
/ \
4 5
注意2是重复的。第一下1,和第二下6. 我的递归是:
with cte (ParentId, ChildId, Field1, Field2) AS (
select BOM.ParentId, BOM.ChildId, BOM.Field1, BOM.Field2
from BillOfMaterials BOM
WHERE ParentId=x
UNION ALL
SELECT BOM.ParentId, BOM.ChildId, BOM.Field1, BOM.Field2 FROM BillOfMaterials BOM
JOIN cte on BOM.ParentId = cte.ChildId
)
select * from cte;
但问题是,在结果关系2-4和2-5是重复的(首先从关系1-2和第二从关系6 -2):
ParentId ChildId OtherFields
1 2
1 3
2 4 /*from 1-2*/
2 5 /*from 1-2*/
3 6
6 2
2 4 /*from 6-2*/
2 5 /*from 6-2*/
有什么办法可以跳过访问重复的关系吗?我没有看到任何逻辑为什么递归运行在已经在结果中的行上。它会更快。类似的东西:
with cte (ParentId, ChildId, Field1, Field2) AS (
select BOM.ParentId, BOM.ChildId, BOM.Field1, BOM.Field2
from BillOfMaterials BOM
WHERE ParentId=x
UNION ALL
SELECT BOM.ParentId, BOM.ChildId, BOM.Field1, BOM.Field2 FROM BillOfMaterials BOM
JOIN cte on BOM.ParentId = cte.ChildId
------> WHERE (select count(*) FROM SoFarCollectedResult WHERE ParentId=BOM.ParentId AND ChildId=BOM.ChildId) = 0
)
select * from cte;
我发现this thread,但它是8岁。
我使用SQL Server 2016
如果这是不可能的,那么我的问题是如何从最终结果中删除重复,但检查不同的只是在的ParentId和childID的列?
编辑:
预期的结果是:
ParentId ChildId OtherFields
1 2
1 3
2 4
2 5
3 6
6 2
从改变你上次查询:
要:
select * from cte group by ParentId, ChildId;
这实际上将采取你现在所拥有的,但更进一步,并删除已经出现的行,这将照顾你的重复问题。只是要确保所有*
返回这里是ParentId
和ChildId
,是否应该回到你要么需要将它们添加到GROUP BY
或应用某种聚合到它,这样它仍然可以组(最大,最小,计数等栏目。 ..)。
你应该有,你不能对总体或一组多个行,你可以编写查询这样:
select * from cte where ID in (select MAX(ID) from cte group by ParentId, ChildId);
凡ID
将是CTE主表的id。这将需要当行相匹配的最大的ID,这通常是你最新的入门,如果你想最早进入只是改变MAX()
到MIN()
。
可以,用增加2点小动作的SQL。
但你需要一个序列号一个额外的ID列。
例如,通过身份或日期时间字段显示记录何时插入。
对于原因很简单,就数据库而言,还有当他们插入,除非你有一列,指出为了在记录中没有任何订单。
窍门1)仅将CTE记录加入更高的Id。因为如果他们低于那些是你不想加入的副本。
招数二)使用窗口函数ROW_NUMBER得到的只有那些最接近标识递归从
开始举例:
declare @BillOfMaterials table (Id int identity(1,1) primary key, ParentId int, ChildId int, Field1 varchar(8), Field2 varchar(8));
insert into @BillOfMaterials (ParentId, ChildId, Field1, Field2) values
(1,2,'A','1-2'),
(1,3,'B','1-3'),
(2,4,'C','2-4'), -- from 1-2
(2,5,'D','2-5'), -- from 1-2
(3,6,'E','3-6'),
(6,2,'F','6-2'),
(2,4,'G','2-4'), -- from 6-2
(2,5,'H','2-5'); -- from 6-2
;with cte AS
(
select Id as BaseId, 0 as Level, BOM.*
from @BillOfMaterials BOM
WHERE ParentId in (1)
UNION ALL
SELECT CTE.BaseId, CTE.Level + 1, BOM.*
FROM cte
JOIN @BillOfMaterials BOM on (BOM.ParentId = cte.ChildId and BOM.Id > CTE.Id)
)
select ParentId, ChildId, Field1, Field2
from (
select *
--, row_number() over (partition by BaseId, ParentId, ChildId order by Id) as RNbase
, row_number() over (partition by ParentId, ChildId order by Id) as RN
from cte
) q
where RN = 1
order by ParentId, ChildId;
结果:
ParentId ChildId Field1 Field2
-------- ------- ------ ------
1 2 A 1-2
1 3 B 1-3
2 4 C 2-4
2 5 D 2-5
3 6 E 3-6
6 2 F 6-2
无论如何,作为旁注,通常使用不同的父子关系表。
更多的时候,它只是一个具有唯一父 - 子组合的表,这些组合是外键给另一个表,其中Id是主键。所以其他字段保存在另一个表中。
如何区分6的1和2孩子的2个孩子? –
您的数据有瑕疵。你不能像那样把它们放在一起。你怎么知道一组2,4属于1,另一组属于6以下。如果你需要这种关系,你需要重建你的数据结构,因为作为一个标准的父母孩子,它不会工作。 –
这里有一个基本问题。树可以根据不同的方法遍历。没有真正意义上的“第一” - 谁来说哪个节点出现应该得到孩子。节点不能'实例化',然后通过他们的祖先进行区分,这使得它们不再是节点。 – Greenspark