34,838
社区成员




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
--创建临时表
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