对于递归函数，请使用CTE而不是表变量

Question

我正在尝试创建一个循环，当给定零件id时，它将搜索一个装配零件表，并将所有零件放入返回表中。即，我想从一个零件id中分解零件。

它需要是递归的，因为第1部分可能有第2、3、4、5部分；部件3是具有部件9、10的组装项目；部件10是具有部件11、23、34、31的组装件；部件11是具有部件23、24的组装件。

数据库是SQL Server。

我在这里设置了一些示例日期：http://sqlfiddle.com/#！9/f3cc4f

我期待结果：

part, level
1,    0
2,    1
3,    1
4,    1
9,    2
10,   2
11,   3
23,   3
24,   3

下面是我想出的代码。我遇到了一个最大递归错误，即使样本数据只有几个级别。我的完整数据集应该不超过15个级别。显然有些设置不正确，我认为CTE可能会更好。

CREATE FUNCTION [dbo].[fn_getParts] (@source_part_id int, @level int)
RETURNS @parts_list TABLE (
[part]  int NOT NULL,
[level] int NOT NULL
)
AS 
BEGIN
DECLARE
@max    int = 0,
@cnt    int = 0,
@PID    int = 0,
@Plvl   int = 0,
@id     int = 0
DECLARE @chkParts table ([id] int identity(1,1), [PID] int, [level] int)
INSERT INTO @parts_list VALUES (@source_part_id, @level)
SET @level += 1
INSERT INTO @chkParts
SELECT [Comp_PartID], @level FROM /*visuser.[EN_BOM]*/ [Assemblies] WHERE [PartID] /*[Assembly_Part_ID]*/ = @source_part_id
SELECT @max = COUNT(*) FROM @chkParts
WHILE @cnt <= @max
BEGIN
SELECT @id = [id], @PID = [PID], @Plvl = [level] FROM @chkParts WHERE [id] = @cnt
INSERT INTO @parts_list
SELECT * FROM [fn_getParts](@PID, @Plvl)
SET @cnt += 1
END
RETURN
END

以下是示例数据：

CREATE TABLE Assemblies (
PartID int NOT NULL,
Comp_PartID int NOT NULL
);

INSERT INTO Assemblies VALUES 
(1, 2),  
(1, 3),
(1, 4),
(1, 5),
(1, 6),
(3, 9),
(3, 10),
(10, 11),
(10, 23),
(10, 24),
(10, 31),
(11, 24),
(11, 23);

Answer 1

以下生成的结果与您描述的逻辑匹配，但与您预期的结果不同。也许你的逻辑需要调整一下？

declare @source_part_id int = 1, @level int = 0;
with cte (part, [level])
as (
select @source_part_id part, @level [level]
union all
select Comp_PartID, [level]+1
from Assemblies A
inner join cte C on C.Part = A.PartID
)
select part, [level]
from cte
order by part, [level];

退货：

part    level
1       0
2       1
3       1
4       1
5       1
6       1
9       2
10      2
11      3
23      3
24      3
31      3
24      4
23      4