sql—递归函数中如何使用#tmp表in循环

d7v8vwbk  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(238)

我试图创建一个循环,当给定一个零件id时,它将搜索一个装配零件表,并将所有零件分解成一个大列表。
它需要递归,因为零件123可能有零件1、2、3、4、5,而零件4和5也是装配项。我想我已经想出了一些很好的方法,可以很容易地返回每个项目的part id和part level。然后我发现我不能使用temp表,所以它把我的循环击倒了。
我可以用什么来代替temp表来提供相同的函数呢?

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

    INSERT INTO @parts_list VALUES (@source_part_id, @level)

    SET @level += 1

    SELECT [Comp_Part_ID] AS [PID], @level AS [level]
    INTO #chkParts
    FROM [assemblies]
    WHERE [Assy_PID] = @source_part_id

    SELECT @max = COUNT(*) FROM #chkParts
    WHILE @cnt <= @max
    BEGIN
        SELECT @PID = [PID], @Plvl = [level] FROM #chkParts
        INSERT INTO @parts_list
            SELECT * FROM [fn_getParts](@PID, @Plvl)
        SET @cnt += 1
    END

    RETURN
END

以下是一些示例数据:

CREATE TABLE [Assemblies] (
  [PartID] int,
  [Comp_PartID] int
  )

  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)

如果我进去 SELECT * FROM [fn_getParts](1,0) 我期望如下:

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

z8dt9xmd1#

通过围绕递归cte Package 内联表值函数,可以在一定程度上简化代码,例如:

create function dbo.fn_getParts (
    @source_part_id int
)
returns table as return (
    with PartsHierarchy as (
      select @source_part_id as part, 0 as level
      union all
      select Comp_PartID, 1 + level
      from Assemblies
      join PartsHierarchy on part = PartID
    )
    select part, level
    from PartsHierarchy
);

然后,对不同的零件号调用它。。。

select * from dbo.fn_getParts(1);
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
select * from dbo.fn_getParts(10);
part level
---- -----
  10    0
  11    1
  23    1
  24    1
  31    1
  24    2
  23    2
select * from dbo.fn_getParts(11);
part level
---- -----
  11    0
  24    1
  23    1

相关问题