多层BOM转换为多个一层BOM

[weisai]

SQL Server数据库中一个表中有如下的2列，表示BOM的多个层次结构。
第一列是层次结构，第二列是物料编码。
需要把这个BOM的多级层级，转换成多个1层的BOM。

多层BOM结构：
层次    子项物料代码
.0     00.19.18.00141
.1        40.00.00.00902
..2    50.00.00.04134
..2    50.00.00.04135
..2    50.00.00.08302
..2    50.00.00.08304
...3    60.00.00.03311
..2    50.00.00.08309
...3    60.00.00.01068
..2    50.00.00.08310
..2    50.00.00.08311
..2    50.00.00.08319
.1        40.00.00.00903

需要查询返回的结果是
父项物料代码    子项物料代码    层级
00.19.18.00141    40.00.00.00902    1
00.19.18.00141    40.00.00.00903    1
40.00.00.00902    50.00.00.04134    2
40.00.00.00902    50.00.00.04135    2
40.00.00.00902    50.00.00.08302    2
40.00.00.00902    50.00.00.08304    2
40.00.00.00902    50.00.00.08309    2
40.00.00.00902    50.00.00.08310    2
40.00.00.00902    50.00.00.08311    2
40.00.00.00902    50.00.00.08319    2
50.00.00.08304    60.00.00.03311    3
50.00.00.08309    60.00.00.01068    3

 

[雨夹雪]

@weisai 人呢?

[weisai]

deepseek，kimi都试过，结果都不对

[雨夹雪]

--创建临时表
CREATE TABLE #tmp
(
层次 VARCHAR(10),    
子项物料代码 VARCHAR(50)
)
 
CREATE TABLE #rst
(
父项物料代码 VARCHAR(50),
子项物料代码 VARCHAR(50)
)
 
--添加测试数据
INSERT INTO #tmp VALUES('.0',     '00.19.18.00141')
INSERT INTO #tmp VALUES('.1',     '40.00.00.00902')
INSERT INTO #tmp VALUES('..2',    '50.00.00.04134')
INSERT INTO #tmp VALUES('..2',    '50.00.00.04135')
INSERT INTO #tmp VALUES('..2',    '50.00.00.08302')
INSERT INTO #tmp VALUES('..2',    '50.00.00.08304')
INSERT INTO #tmp VALUES('...3',   '60.00.00.03311')
INSERT INTO #tmp VALUES('..2',    '50.00.00.08309')
INSERT INTO #tmp VALUES('...3',   '60.00.00.01068')
INSERT INTO #tmp VALUES('...3',   '60.00.00.01069')
INSERT INTO #tmp VALUES('..2',    '50.00.00.08310')
INSERT INTO #tmp VALUES('..2',    '50.00.00.08311')
INSERT INTO #tmp VALUES('..2',    '50.00.00.08319')
INSERT INTO #tmp VALUES('.1',     '40.00.00.00903')
INSERT INTO #tmp VALUES('..2',    '40.00.00.00909')
 
--开始找父级
DECLARE @id INT =1
DECLARE @cnt INT =0
DECLARE @cc VARCHAR(50)=''
DECLARE @cc_old VARCHAR(50)=''
DECLARE @f VARCHAR(50)=''
DECLARE @wl VARCHAR(50)=''
 
--你的数据要保证顺序且层次必须规则这里需要比较大小
/*
思路:
从上往下,一行一行开始找
如果相邻的层级发生变化,就取小于这个层级id最大的这个作为父级
如果相邻的层级没变化,则父级不发生变化
*/
SELECT RIGHT('..........'+层次,10) AS 层次,子项物料代码 ,IDENTITY(INT,1,1) AS id INTO #tmp2 
FROM #tmp  
SET @cnt=@@ROWCOUNT

 
 
WHILE @id<=@cnt 
BEGIN  
    SELECT @cc=层次,@wl=子项物料代码 FROM #tmp2 WHERE id=@id 
    
    IF @cc<>@cc_old
    BEGIN 
        SELECT TOP 1 @f=子项物料代码 FROM #tmp2 WHERE 层次<@cc  AND id<@id
        ORDER  BY id  desc
    END 
     
    INSERT INTO #rst (父项物料代码, 子项物料代码) VALUES(@f,@wl)
    
    SET @cc_old=@cc
    SET @id=@id+1
END  
 
--展开即可 
;WITH ct
AS
(
    SELECT *,CONVERT(INT,0 )AS 层次,CONVERT(VARCHAR(max),子项物料代码) AS cpath  FROM #rst WHERE 父项物料代码 =''
    UNION ALL
    SELECT a.子项物料代码,b.子项物料代码,层次+1,CONVERT(VARCHAR(max),cpath+'->'+b.子项物料代码)  
    FROM ct a INNER JOIN #rst b ON  a.子项物料代码 = b.父项物料代码
)
SELECT  * FROM ct
ORDER BY cpath 
 
 
--删除临时表
DROP TABLE #tmp2
DROP TABLE #rst
DROP TABLE #tmp