sybase ASA BOM的递归写法 与 SQL的递归写法

  http://www.2cto.com/database/201403/287116.html">http://www.2cto.com/database/201403/287116.html 引用网址1：http://blog.csdn.net/feixianxxx/article/details/4753783">http://blog.csdn.net/feixianxxx/article/details/4753783

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)) sortfrom 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 tinner 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   螺丝  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*/

 

 

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_bomWHERE  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,       bomWHERE  bom.no_1 = new_bom.no                                                                           --下一级阶层=此层的BOM工程  重点) select * from bom where no='0001' order by operation_seq,rown</p>