求一条SQL语句，将BOM按层次结构显示。

2011-03-16 01:05:20 974 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

ERP_用友U8后台SQL批量查询BOM ERP_用友U8后台SQL批量查询BOM 1、BOM批量查询在U8前端无法全部输出；这是用户端非常苦恼的一件事且因为购买加密站点的原因；普通用户查询或者导出一个BOM是非常困难的，也考虑到公司机密权限的问题；故自定义开发批量查询BOM的报表； SQL: select [母件资料].BomId as [母件资料ID ],[母件资料].ParentId as [母件物料Id],[物料表].InvCode 母件料号 ,[存货档案1].cInvName AS [母件物料名], [存货档案1].cInvStd AS 阅读详情

原表数据，
（有一个BOM，所有的成品，半成品都放在一张表里面，在这里没有层次区分，所有的数据都在这张表中）

成品编号 BOMID 行号物料编码用量类型
A-001 BOM-1 1 1001 1 物料
A-001 BOM-1 2 1003 3 半成品
A-001 BOM-1 3 1004 2 半成品
A-001 BOM-1 4 1005 1 物料
A-001 BOM-1 5 1006 2 物料
B-001 BOM-2 1 1001 1 物料
B-001 BOM-2 2 1003 5 半成品
B-001 BOM-2 3 1009 2 物料
1003 BOM-32 1 3301 0.3 半成品
1003 BOM-32 2 3302 3 物料
1003 BOM-32 3 3303 2 物料
3301 BOM43 1 98001 物料
3301 BOM43 2 98001 物料
3301 BOM43 3 98001 物料
3301 BOM43 4 98001 物料
3301 BOM43 5 98001 物料

怎样将上表的数据变成树状结构的下图所示的，（这个表里面很多BOM，是否能一次性将所有的BOM按下列的方式树状方式显示出来。）

成品编号层次 BOMID 行号物料编码用量类型
A-001 1 BOM-1 1 1001 1 物料
A-001 1 BOM-1 2 1003 3 半成品
A-001 1.1 BOM-32 1 3301 0.3 半成品
A-001 1.1.1 BOM43 1 98001 物料
A-001 1.1.2 BOM43 2 98001 物料
A-001 1.1.3 BOM43 3 98001 物料
A-001 1.1.4 BOM43 4 98001 物料
A-001 1.1.5 BOM43 5 98001 物料
A-001 1.2 BOM-32 2 3302 3 物料
A-001 1.3 BOM-32 3 3303 2 物料
A-001 1 BOM-1 3 1004 2 半成品
A-001 1 BOM-1 4 1005 1 物料
A-001 1 BOM-1 5 1006 2 物料

...全文

取得鼎新Workflow ERP系统产品结构BOM明细的SQL语句产品结构BOM,是制造企业ERP系统的核心，订产品开发、订单报价、原物料采购、制令生产等等都以它为基础进行运算。以下是鼎新ERP为例，给出在SQL后台如何取得一个产成品BOM明细的代码示例： declare @maincode char(60) set @maincode='xxxxxx'--产成品品号 declare @i int set @i=0 --定义主件品号临时表阅读详情

Justin_chen

应用实例 27,579 成员 68,540 内容

+ 关注 + 加入社区

切换为时间正序

dawugui 2011-03-16

打赏
举报



/*

标题：SQL SERVER 2005中树结构按照各顶级节点排序

作者：爱新觉罗·毓华(十八年风雨,守得冰山雪莲花开) 

时间：2010-10-24

地点：陕西西安

说明：

如下树级结构中需要按照各顶级节点排序

id   pid  name

---- ---- ----------

001  000  广东省

002  001  广州市

003  001  深圳市

004  002  天河区

005  003  罗湖区

006  003  福田区

007  003  宝安区

008  007  西乡镇

009  007  龙华镇

010  007  松岗镇

011  000  江西省

012  011  南昌市

013  012  南昌县

014  012  新建县

015  012  进贤县

016  012  安义县

017  013  麻丘镇

018  011  九江市

需要的结果：

id   name       pid

---- ---------- ----

001  广东省     000

002  广州市     001

004  天河区     002

003  深圳市     001

005  罗湖区     003

006  福田区     003

007  宝安区     003

008  西乡镇     007

009  龙华镇     007

010  松岗镇     007

011  江西省     000

012  南昌市     011

013  南昌县     012

017  麻丘镇     013

014  新建县     012

015  进贤县     012

016  安义县     012

018  九江市     011

*/



create table tb(id varchar(3) , pid varchar(3) , name nvarchar(10))

insert into tb values('001' , '000' , N'广东省')

insert into tb values('002' , '001' , N'广州市')

insert into tb values('003' , '001' , N'深圳市')

insert into tb values('004' , '002' , N'天河区')

insert into tb values('005' , '003' , N'罗湖区')

insert into tb values('006' , '003' , N'福田区')

insert into tb values('007' , '003' , N'宝安区')

insert into tb values('008' , '007' , N'西乡镇')

insert into tb values('009' , '007' , N'龙华镇')

insert into tb values('010' , '007' , N'松岗镇')

insert into tb values('011' , '000' , N'江西省')

insert into tb values('012' , '011' , N'南昌市')

insert into tb values('013' , '012' , N'南昌县')

insert into tb values('014' , '012' , N'新建县')

insert into tb values('015' , '012' , N'进贤县')

insert into tb values('016' , '012' , N'安义县')

insert into tb values('017' , '013' , N'麻丘镇')

insert into tb values('018' , '011' , N'九江市')

go



;with t as

(

    select px = cast(id as varchar(500)) , * from tb t 

    where not exists(select 1 from tb where id = t.pid)

    union all

    select cast(b.px + ',' + a.id as varchar(500)) , a.*

    from tb a , t b where a.pid = b.id

)

select id , name , pid from t order by px



drop table tb

pt1314917 2011-03-16

打赏
举报

--> 测试数据: [tb]

if object_id('[tb]') is not null drop table [tb]

create table [tb] (成品编号 varchar(5),BOMID varchar(6),行号 int,物料编码 int,用量 numeric(2,1),类型 varchar(6))

insert into [tb]

select 'A-001','BOM-1',1,1001,1,'物料' union all

select 'A-001','BOM-1',2,1003,3,'半成品' union all

select 'A-001','BOM-1',3,1004,2,'半成品' union all

select 'A-001','BOM-1',4,1005,1,'物料' union all

select 'A-001','BOM-1',5,1006,2,'物料' union all

select 'B-001','BOM-2',1,1001,1,'物料' union all

select 'B-001','BOM-2',2,1003,5,'半成品' union all

select 'B-001','BOM-2',3,1009,2,'物料' union all

select '1003','BOM-32',1,3301,0.3,'半成品' union all

select '1003','BOM-32',2,3302,3,'物料' union all

select '1003','BOM-32',3,3303,2,'物料' union all

select '3301','BOM43',1,98001,null,'物料' union all

select '3301','BOM43',2,98001,null,'物料' union all

select '3301','BOM43',3,98001,null,'物料' union all

select '3301','BOM43',4,98001,null,'物料' union all

select '3301','BOM43',5,98001,null,'物料'



select * from [tb]







;with wsp

as

(

select 成品编号,层次=cast(1 as varchar),px=cast(行号 as varchar),BOMID,行号,物料编码,用量,类型 from tb b 

where not exists(select 1 from tb where ltrim(物料编码)=b.成品编号) and 成品编号='A-001'

union all

select a.成品编号,层次=cast(a.层次+'.'+ltrim(row_number() over(order by b.行号)) as varchar),

px=cast(a.px+'.'+ltrim(row_number() over(order by b.行号)) as varchar),b.BOMID,b.行号,b.物料编码,b.用量,b.类型

 from wsp a,tb b where ltrim(a.物料编码)=b.成品编号

)

select 成品编号,层次,BOMID,行号,物料编码,用量,类型 from wsp order by px



--结果：

成品编号  BOMID  行号          物料编码        用量                                      类型

----- ------ ----------- ----------- --------------------------------------- ------

A-001 BOM-1  1           1001        1.0                                     物料

A-001 BOM-1  2           1003        3.0                                     半成品

A-001 BOM-1  3           1004        2.0                                     半成品

A-001 BOM-1  4           1005        1.0                                     物料

A-001 BOM-1  5           1006        2.0                                     物料

B-001 BOM-2  1           1001        1.0                                     物料

B-001 BOM-2  2           1003        5.0                                     半成品

B-001 BOM-2  3           1009        2.0                                     物料

1003  BOM-32 1           3301        0.3                                     半成品

1003  BOM-32 2           3302        3.0                                     物料

1003  BOM-32 3           3303        2.0                                     物料

3301  BOM43  1           98001       NULL                                    物料

3301  BOM43  2           98001       NULL                                    物料

3301  BOM43  3           98001       NULL                                    物料

3301  BOM43  4           98001       NULL                                    物料

3301  BOM43  5           98001       NULL                                    物料



(16 行受影响)



成品编号  层次                             BOMID  行号          物料编码        用量                                      类型

----- ------------------------------ ------ ----------- ----------- --------------------------------------- ------

A-001 1                              BOM-1  1           1001        1.0                                     物料

A-001 1                              BOM-1  2           1003        3.0                                     半成品

A-001 1.1                            BOM-32 1           3301        0.3                                     半成品

A-001 1.1.1                          BOM43  1           98001       NULL                                    物料

A-001 1.1.2                          BOM43  2           98001       NULL                                    物料

A-001 1.1.3                          BOM43  3           98001       NULL                                    物料

A-001 1.1.4                          BOM43  4           98001       NULL                                    物料

A-001 1.1.5                          BOM43  5           98001       NULL                                    物料

A-001 1.2                            BOM-32 2           3302        3.0                                     物料

A-001 1.3                            BOM-32 3           3303        2.0                                     物料

A-001 1                              BOM-1  3           1004        2.0                                     半成品

A-001 1                              BOM-1  4           1005        1.0                                     物料

A-001 1                              BOM-1  5           1006        2.0                                     物料

--小F-- 2011-03-16

打赏
举报

--参考一下实例

--> 生成测试数据表:tb

IF NOT OBJECT_ID('[tb]') IS NULL

 DROP TABLE [tb]

GO

CREATE TABLE [tb](GUID INT IDENTITY,[col1] NVARCHAR(10),[col2] NVARCHAR(20))

INSERT [tb]

SELECT N'A','01' UNION ALL

SELECT N'B','01.01' UNION ALL

SELECT N'C','01.01.01' UNION ALL

SELECT N'F','01.01.01.01' UNION ALL

SELECT N'E','01.01.01.02' UNION ALL

SELECT N'D','01.01.01.03' UNION ALL

SELECT N'O','02' UNION ALL

SELECT N'P','02.01' UNION ALL

SELECT N'Q','02.01.01' 

GO

--SELECT * FROM [tb]



-->SQL查询如下:



---另一种方法

;WITH T AS

(

   SELECT *,PATH=CAST([COL1] AS VARCHAR(1000)) FROM TB A

       WHERE NOT EXISTS(

        SELECT 1 FROM TB 

     WHERE A.COL2 LIKE COL2+'%' 

   AND LEN(A.COL2)>LEN(COL2))

   UNION ALL

   SELECT A.*,CAST(PATH+'-->'+A.COL1 AS VARCHAR(1000))

   FROM TB A 

   JOIN T B 

        ON A.COL2 LIKE B.COL2+'%' 



           AND LEN(A.COL2)-3=LEN(B.COL2)

)



SELECT * FROM T ORDER BY LEFT(COL2,2)



/*



GUID        COL1        COL2                  PATH



----------- ---------- -------------------- --------------------



1           A          01                   A



2           B          01.01                A-->B



3           C          01.01.01             A-->B-->C



4           F          01.01.01.01          A-->B-->C-->F



5           E          01.01.01.02          A-->B-->C-->E



6           D          01.01.01.03          A-->B-->C-->D



7           O          02                   O



8           P          02.01                O-->P



9           Q          02.01.01             O-->P-->Q

(9 行受影响)



*/





;WITH T AS



(

    SELECT *,CAST(COL1  AS VARCHAR(1000)) AS PATH

    FROM  TB 

    WHERE COL2 NOT LIKE '%.%'

    UNION ALL

    SELECT A.*,CAST(B.PATH+'-->'+A.COL1 AS VARCHAR(1000))

    FROM TB A,T B

    WHERE A.COL2 LIKE B.COL2+'.[01-99][01-99]'

)



SELECT * FROM T 

ORDER BY LEFT(COL2,2)



/*



GUID        COL1        COL2                  PATH



----------- ---------- -------------------- --------------------



1           A          01                   A



2           B          01.01                A-->B



3           C          01.01.01             A-->B-->C



4           F          01.01.01.01          A-->B-->C-->F



5           E          01.01.01.02          A-->B-->C-->E



6           D          01.01.01.03          A-->B-->C-->D



7           O          02                   O



8           P          02.01                O-->P



9           Q          02.01.01             O-->P-->Q



 (9 行受影响)



*/

--小F-- 2011-03-16

打赏
举报

参考:

BOM按节点排序应用实例 

--------------------------------------------------------------------------



--  Author : htl258(Tony)



--  Date   : 2010-04-23 02:37:28



--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 



--          Jul  9 2008 14:43:34 



--          Copyright (c) 1988-2008 Microsoft Corporation



--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)



--  Subject: BOM按节点排序应用实例



--------------------------------------------------------------------------



 



--实例1:



--> 生成测试数据表:tb



 



IF NOT OBJECT_ID('[tb]') IS NULL



    DROP TABLE [tb]



GO



CREATE TABLE [tb]([id] INT,[code] NVARCHAR(10),[pid] INT,[name] NVARCHAR(10))



INSERT [tb]



SELECT 1,'01',0,N'服装' UNION ALL



SELECT 2,'01',1,N'男装' UNION ALL



SELECT 3,'01',2,N'西装' UNION ALL



SELECT 4,'01',3,N'全毛' UNION ALL



SELECT 5,'02',3,N'化纤' UNION ALL



SELECT 6,'02',2,N'休闲装' UNION ALL



SELECT 7,'02',1,N'女装' UNION ALL



SELECT 8,'01',7,N'套装' UNION ALL



SELECT 9,'02',7,N'职业装' UNION ALL



SELECT 10,'03',7,N'休闲装' UNION ALL



SELECT 11,'04',7,N'西装' UNION ALL



SELECT 12,'01',11,N'全毛' UNION ALL



SELECT 13,'02',11,N'化纤' UNION ALL



SELECT 14,'05',7,N'休闲装'



GO



--SELECT * FROM [tb]



 



-->SQL查询如下:



 



;WITH T AS



(



    SELECT CAST(CODE AS VARCHAR(20)) AS CODE,*,



        CAST(ID AS VARBINARY(MAX)) AS px 



    FROM tb AS A



    WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.pid)



    UNION ALL 



    SELECT CAST(B.CODE+A.CODE AS VARCHAR(20)),A.*,



         CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX))    



    FROM tb AS A



        JOIN T AS B



           ON A.pid=B.id



)



SELECT Code,Name FROM T 



ORDER BY px



/*



Code                 Name



-------------------- ----------



01                   服装



0101                 男装



010101               西装



01010101             全毛



01010102             化纤



010102               休闲装



0102                 女装



010201               套装



010202               职业装



010203               休闲装



010204               西装



01020401             全毛



01020402             化纤



010205               休闲装



 



(14 行受影响)



*/



 



--实例2:



--> 生成测试数据表:tb



IF NOT OBJECT_ID('[tb]') IS NULL

    DROP TABLE [tb]

GO

CREATE TABLE [tb]([id] INT,[parentid] INT,[categoryname] NVARCHAR(10))

INSERT [tb]

SELECT 1,0,'test1' UNION ALL

SELECT 2,0,'test2' UNION ALL

SELECT 3,1,'test1.1' UNION ALL

SELECT 4,2,'test2.1' UNION ALL

SELECT 5,3,'test1.1.1' UNION ALL

SELECT 6,1,'test1.2'

GO

--SELECT * FROM [tb]



-->SQL查询如下:

;WITH T AS

(

    SELECT *,CAST(ID AS VARBINARY(MAX)) AS px 

    FROM tb AS A

    WHERE NOT EXISTS(SELECT * FROM tb WHERE id=A.[parentid])

    UNION ALL 

    SELECT A.*,CAST(B.px+CAST(A.ID AS VARBINARY) AS VARBINARY(MAX))  

    FROM tb AS A

        JOIN T AS B

           ON A.[parentid]=B.id

)

SELECT [id],[parentid],[categoryname] FROM T 

ORDER BY px

/*

id          parentid    categoryname

----------- ----------- ------------

1           0           test1

3           1           test1.1

5           3           test1.1.1

6           1           test1.2

2           0           test2

4           2           test2.1



(6 行受影响)

*/







本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/htl258/archive/2010/04/23/5518166.aspx

求一条SQL语句，将BOM按层次结构显示。

相关推荐