http://www.cnblogs.com/smailxiaobai/archive/2012/01/16/2323291.html 递归的查询非常的好文章
http://blog.csdn.net/feixianxxx/article/details/4753783
http://www.yesky.com/20010919/197786_1.shtml
http://masterpiece.cnblogs.com/archive/2006/03/14/82588.html MRP的算法
http://blog.sina.com.cn/s/blog_6ed50cc50100n3ls.html MRP算法
问题是这样的: 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] GOINSERT 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 螺丝 11001 水龙头 10063 水管 110063 水管 102331 塑料 1-110063 水管 102303 胶皮 1-2102303 胶皮 203301 胶水 1-2-1102303 胶皮 203302 布料 1-2-21001 水龙头 10082 扳手 110082 扳手 205410 铁柄 1-110082 扳手 205418 铁头 1-21001 水龙头 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
*/
BOM:
的数据源
<p>with RECURSIVE BOM(bzlevern,no,当成工程号产品,part_id,no_1,--leverN,
usage,ROWN,operation_seq,line_no) as (
SELECT 1 AS bzlevern,--等级层次标记
no,
no as 当成工程号产品, --主产品
part_id, --材料产品
next_egm_no AS no_1, --主产品
-- Cast(1 AS VARCHAR(100)) AS lever, --
em_bom.usage, --材料的用量
Cast(Row_number() OVER(partition BY no ORDER BY operation_seq, line_no) AS VARCHAR(100)) sort, --统计一层数量为标记
em_bom.operation_seq,line_no --工序序号,序号
FROM em_bom
WHERE no IN(SELECT part_id --no=part_id 但如果不是相等那么需要连接主工程表 select * from engineer_master,part where part.part_id=parent_part_id
FROM part
WHERE location_id = 'CP') --显示成品的一层的产品</p><p>UNION ALL
SELECT bzlevern=bom.bzlevern + 1, --增加一层先
bom.no,
new_bom.no as 当成工程号产品, --主产品
new_bom.part_id, --材料代号
new_bom.next_egm_no AS no_1, --下层代号NO
--Cast(bom.leverN + '.' + Cast(new_bom.rownum AS VARCHAR(100)) AS VARCHAR(100)), --单层测试
bom.usage*new_bom.usage , --总用量*单件用量
Cast(bom.ROWN + '.' + Cast(new_bom.rownum AS VARCHAR(100)) AS VARCHAR(100)), --层次
bom.operation_seq,new_bom.line_no --工序层次,物料序号
FROM (SELECT *,
Row_number() OVER (partition BY EM_bom.NO ORDER BY line_no) AS rownum
FROM EM_BOM) new_bom,
bom
WHERE bom.no_1 = new_bom.no --下一级阶层=此层的BOM工程 重点
)
select * from bom where no='0001' order by operation_seq,rown</p>