有一张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')
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')
(select model from bom n where charindex(m.goodscode,n.parentitem) > 0) modelB
from BOM m
where m.parentitem is null
from bom A
join B on A.parentitem is null and b.parentitem is not null
and A.itemcode=B.parentitem
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
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')
goselect 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
-- 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 行受影响)*/