求一BOM结构视图，高手请进。

2009-12-29 08:43:21 148 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

flyfly2008 墨衍会员 · AI 创作全网分发关注 + 加入社区

有一张Bom表：

goodscode parentitem itemcode model
F0000023 NULL F0000023.1 09864R
SF-7075-*-0-126-2 F0000023.1 SF-7075-*-0-126-2.1 7075
SF-9387-*-0-126-2 F0000023.1 SF-9387-*-0-126-2.1 9387
SF-8971-*-0-126-2 F0000023.1 SF-8971-*-0-126-2.1 8971
F0000043 NULL F0000043.1 09864Q
SF-8971-0-0-222-2 F0000043.1 SF-8971-0-0-222-2.1 8971
SF-7075-0-0-222-2 F0000043.1 SF-7075-0-0-222-2.1 7075
SF-9387-0-0-222-2 F0000043.1 SF-9387-0-0-222-2.1 9387

我要得到如下内容视图：
modelA modelB
09864R 7075
09864R 9387
09864R 8971
09864Q 8971
09864Q 7075
09864Q 9387

就是根据BOM结构关系，把model项一列分解成两列。
参加他们之间关系：select * from Bom where parentitem in (select itemcode from Bom where model='09864Q')

...全文

【VC基础】2、物料主档与BOM结构物料主档与BOM结构通常来说Super BOM都有复杂的结构，比如下图所示，就是PP模块中超级BOM比较复杂的一种，下面我们就模拟下图的方式来创建物料和BOM结构。【Example】一、物料创建 1、创建原材料（组件物料）事务代码：MM01 行业领域：M 机械工程物料类型：ROH 原材料组件物料需要基本数据视图、采购视图、MRP视图和会计成本视图，不需要分类视图。按要求依次创建如下原材料类型物料。硬盘物料 ... 阅读详情

flyfly2008

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

playwarcraft 2009-12-29

打赏
举报

只有2层？

--小F-- 2009-12-29

打赏
举报

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

-- Author  :fredrickhu(我是小F，向高手学习)

-- Date    :2009-12-29 09:07:16

-- Version:

--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 

--	Nov 24 2008 13:01:59 

--	Copyright (c) 1988-2005 Microsoft Corporation

--	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

--

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

--> 测试数据：[tb]

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

go 

create table [tb]([goodscode] varchar(17),[parentitem] varchar(10),[itemcode] varchar(19),[model] varchar(6))

insert [tb]

select 'F0000023',null,'F0000023.1','09864R' union all

select 'SF-7075-*-0-126-2','F0000023.1','SF-7075-*-0-126-2.1','7075' union all

select 'SF-9387-*-0-126-2','F0000023.1','SF-9387-*-0-126-2.1','9387' union all

select 'SF-8971-*-0-126-2','F0000023.1','SF-8971-*-0-126-2.1','8971' union all

select 'F0000043',null,'F0000043.1','09864Q' union all

select 'SF-8971-0-0-222-2','F0000043.1','SF-8971-0-0-222-2.1','8971' union all

select 'SF-7075-0-0-222-2','F0000043.1','SF-7075-0-0-222-2.1','7075' union all

select 'SF-9387-0-0-222-2','F0000043.1','SF-9387-0-0-222-2.1','9387'

--------------开始查询--------------------------

select

 A.model as modelA,

 B.model as modelB

from

 tb a

join tb b on 

 a.parentitem is null 

and

 b.parentitem is not null

and

 a.itemcode=b.parentitem

and

 charindex(a.goodscode , b.parentitem) > 0





----------------结果----------------------------

/* modelA modelB

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

09864R 7075

09864R 9387

09864R 8971

09864Q 8971

09864Q 7075

09864Q 9387



(6 行受影响)



*/

dawugui 2009-12-29

打赏
举报

[Quote=引用 1 楼 dawugui 的回复:]
SQL codeselect m.modelA
(select modelfrom bom nwherecharindex(m.goodscode,n.parentitem)>0) modelBfrom BOM mwhere m.parentitemisnull
[/Quote]
我一楼写错了,不好意思.

create table Bom(goodscode  varchar(50),      parentitem varchar(50),           itemcode  varchar(50),            model varchar(50))

insert into bom values('F0000023'         , NULL        , 'F0000023.1'         , '09864R') 

insert into bom values('SF-7075-*-0-126-2', 'F0000023.1', 'SF-7075-*-0-126-2.1', '7075') 

insert into bom values('SF-9387-*-0-126-2', 'F0000023.1', 'SF-9387-*-0-126-2.1', '9387') 

insert into bom values('SF-8971-*-0-126-2', 'F0000023.1', 'SF-8971-*-0-126-2.1', '8971') 

insert into bom values('F0000043'         , NULL        , 'F0000043.1'         , '09864Q') 

insert into bom values('SF-8971-0-0-222-2', 'F0000043.1', 'SF-8971-0-0-222-2.1', '8971') 

insert into bom values('SF-7075-0-0-222-2', 'F0000043.1', 'SF-7075-0-0-222-2.1', '7075') 

insert into bom values('SF-9387-0-0-222-2', 'F0000043.1', 'SF-9387-0-0-222-2.1', '9387')

go



select m.model modelA ,

       n.model modelB

from BOM m , BOM n 

where m.parentitem is null and m.itemcode = n.parentitem

/*

modelA                                             modelB                                             

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

09864R                                             7075

09864R                                             9387

09864R                                             8971

09864Q                                             8971

09864Q                                             7075

09864Q                                             9387



（所影响的行数为 6 行）

*/



select m.model modelA ,

       n.model modelB

from BOM m , BOM n 

where m.parentitem is null and charindex(m.goodscode , n.parentitem) > 0

/*

modelA                                             modelB                                             

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

09864R                                             7075

09864R                                             9387

09864R                                             8971

09864Q                                             8971

09864Q                                             7075

09864Q                                             9387



（所影响的行数为 6 行）

*/



drop table bom

bancxc 2009-12-29

打赏
举报



select modelA=A.model,modelB=B.model

from bom A

join bom B on A.parentitem is null and b.parentitem is not null

and A.itemcode=B.parentitem

bancxc 2009-12-29

打赏
举报

--只有两层



select modelA=A.model,modelB=B.model

from bom A

join B on A.parentitem is null and b.parentitem is not null

and A.itemcode=B.parentitem

dawugui 2009-12-29

打赏
举报

select m.modelA 

       (select model from bom n where charindex(m.goodscode,n.parentitem) > 0) modelB

from BOM m

where m.parentitem is null

求一BOM结构视图，高手请进。

相关推荐