我有一棵树,树中的特定节点可以出现在树的另一个节点中。(2在我的例子中):
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
发布于 2017-07-28 19:51:05
您可以,添加到SQL中的两个小技巧。
但是您需要一个带有序号的额外Id列。
例如,通过标识或日期时间字段显示何时插入记录。
原因很简单,就数据库而言,在插入记录时,记录中没有顺序,除非您得到了一列指示该顺序的列。
技巧1)只将CTE记录加入到Id较高的位置。因为如果它们较低,那么这些就是你不想加入的副本。
技巧2)使用window函数Row_number,只获取与递归开始时最接近的Id
示例:
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是主键。以便其他字段保存在另一个表中。
发布于 2017-07-28 18:53:10
从以下位置更改最后一个查询:
select * from cte;
至:
select * from cte group by ParentId, ChildId;
这实际上将占用您现在拥有的内容,但是更进一步,删除已经出现的行,这将解决您的重复问题。只需确保这里返回的所有*
都是ParentId
和ChildId
,如果它正在返回其他列,则需要将它们添加到GROUP BY
或向其应用某种聚合器,以便它仍然可以分组(max、min、count.)。
如果有更多无法聚合或分组的行,则可以这样编写查询:
select * from cte where ID in (select MAX(ID) from cte group by ParentId, ChildId);
其中ID
将是cte的主表id。这将在行匹配时获得最大id,通常这将是您的最新条目,如果您希望最早的条目只需将MAX()
更改为MIN()
即可。
https://stackoverflow.com/questions/45380294
复制相似问题