【Transact-SQL】BOM按节点排序

问题是这样的：

CREATE TABLE [dbo].[BOM_Table](
	[Code] [nvarchar](50) NULL,
	[Z_Name] [nvarchar](50) NULL,
	[B_Code] [nvarchar](50) NULL,
	[B_Name] [nvarchar](50) NULL
) ON [PRIMARY]

GO
INSERT INTO BOM_Table(Code,Z_Name,B_Code,B_Name)
VALUES('1001','水龙头','10089','螺丝')
INSERT INTO BOM_Table(Code,Z_Name,B_Code,B_Name)
VALUES('1001','水龙头','10063','水管')
INSERT INTO BOM_Table(Code,Z_Name,B_Code,B_Name)
VALUES('1001','水龙头','10082','扳手')
INSERT INTO BOM_Table(Code,Z_Name,B_Code,B_Name)
VALUES('1001','水龙头','10081','胶带')
INSERT INTO BOM_Table(Code,Z_Name,B_Code,B_Name)
VALUES('10063','水管','102331','塑料')
INSERT INTO BOM_Table(Code,Z_Name,B_Code,B_Name)
VALUES('10063','水管','102303','胶皮')
INSERT INTO BOM_Table(Code,Z_Name,B_Code,B_Name)
VALUES('102303','胶皮','203301','胶水')
INSERT INTO BOM_Table(Code,Z_Name,B_Code,B_Name)
VALUES('102303','胶皮','203302','布料')
INSERT INTO BOM_Table(Code,Z_Name,B_Code,B_Name)
VALUES('10082','扳手','205410','铁柄')
INSERT INTO BOM_Table(Code,Z_Name,B_Code,B_Name)
VALUES('10082','扳手','205418','铁头')

--------结果---------------------------------------------
--格式不限，游标 什么的都可以，只是这只是列举了几条数据而已，深度未知，不一定就是三层或者四层
--希望高手别写死，写死的没啥意思，要活的 动态的  静候各位指点。
---插入一个新表 或者 显示如下格式------

Code	Z_Name	B_Code	B_Name	节点属性
1001	水龙头	10089	螺丝	1
1001	水龙头	10063	水管	1
10063	水管	102331	塑料	1-1
10063	水管	102303	胶皮	1-2
102303	胶皮	203301	胶水	1-2-1
102303	胶皮	203302	布料	1-2-2
1001	水龙头	10082	扳手	1
10082	扳手	205410	铁柄	1-1
10082	扳手	205418	铁头	1-2
1001	水龙头	10081	胶带	1

这个写法，通过not exists找到根节点，然后从根节点开始，向子节点遍历，通过sort字段来排序，而属性字段则是由层次level，加上rownum组合而成：

;with tt 
as 
(
  select *, 
         row_number() over (partition by Code order by getdate()) rownum
  from   [BOM_Table]
)
 
,t as
(
select tt.[Code],tt.[Z_Name],tt.[B_Code],tt.[B_Name],
       cast(1 as varchar(100)) level ,
       CAST(rownum as varchar(100)) sort
from tt 
where not exists(select 1 from tt a where tt.code = a.b_code)

union all

select tt.[Code],tt.[Z_Name],tt.[B_Code],tt.[B_Name],
       cast(t.level+'-'+cast(tt.rownum as varchar(100)) as varchar(100)),
       cast(t.sort+'-'+cast(tt.rownum as varchar(100)) as varchar(100))
from t
inner join tt
        on tt.Code = t.B_Code
)


select [Code],[Z_Name],[B_Code],[B_Name],
       level as 节点属性
from t 
order by sort
/*
Code	Z_Name	B_Code	B_Name	节点属性
1001	水龙头	10089	螺丝	1
1001	水龙头	10063	水管	1
10063	水管	102331	塑料	1-1
10063	水管	102303	胶皮	1-2
102303	胶皮	203301	胶水	1-2-1
102303	胶皮	203302	布料	1-2-2
1001	水龙头	10082	扳手	1
10082	扳手	205410	铁柄	1-1
10082	扳手	205418	铁头	1-2
1001	水龙头	10081	胶带	1
*/