求一SQL语句,用于BOM展开! 有点难度,高手不防来试一试!

Question

有开发或维护过ERP或MRP系统者都应该清楚,在ERP或MRP中有一工程BOM(物料清单),
而BOM是分层的,我想通过某一算法,求得BOM展开后的最后一层物料编号及其用量.例如:BOM(父层编号,子层编号,子层用量)所谓用量:是指这个物料在成品上需要用几个,如一副眼镜,镜片需要用到两个,这两个就指用量.示例数据:    BOM(ItemNo,PartNo,PerQty)
   第一层:
   ItemNo          PartNo            PerQty
   9900DS-U2 067-017140A 1.0000
   9900DS-U2 060-1163          1.0000
   9900DS-U2 062-1717          1.0000
   9900DS-U2 066-9900DSOJL .2500
   9900DS-U2 070-00980          1.0000
   9900DS-U2 068-1017          1.0000
   9900DS-U2 9900-SA-01 2.0000
   
   第二层:
   9900-SA-01 003-SD06095 2.0000
   9900-SA-01 000-2034-1-2B 1.0000
   9900-SA-01 000-2149-1-8C 1.0000
   9900-SA-01 000-2059-1-8C 1.0000
   9900-SA-01 000-2030-1-8C 1.0000
   9900-SA-01 9900-PCB-01 1.0000
   9900-SA-01 9900-PCB-02 1.0000
   
   第三层:
   9900-PCB-01 026-0120210410 2.0000
   9900-PCB-01 023-12260165050 1.0000
   9900-PCB-01 049-F0005          1.0000
   9900-PCB-01 023-11050505050 3.0000
   9900-PCB-01 026-0680210410 1.0000
   9900-PCB-01 036-04060          1.0000   第三层:
   9900-PCB-02 026-0330310410 1.0000
   9900-PCB-02 023-01040257070 4.0000
   9900-PCB-02 023-14770107080 3.0000   
   
   我想要的结果是:   9900DS-U2 067-017140A 1.0000
   9900DS-U2 060-1163          1.0000
   9900DS-U2 062-1717          1.0000
   9900DS-U2 066-9900DSOJL .2500
   9900DS-U2 070-00980          1.0000
   9900DS-U2 068-1017          1.0000   9900DS-U2 003-SD06095 4.0000
   9900DS-U2 000-2034-1-2B 2.0000
   9900DS-U2 000-2149-1-8C 2.0000
   9900DS-U2 000-2059-1-8C 2.0000
   9900DS-U2 000-2030-1-8C 2.0000   9900DS-U2 026-0120210410 2.0000
   9900DS-U2 023-12260165050 1.0000
   9900DS-U2 049-F0005          1.0000
   9900DS-U2 023-11050505050 3.0000
   9900DS-U2 026-0680210410 1.0000
   9900DS-U2 036-04060          1.0000   9900DS-U2 026-0330310410 1.0000
   9900DS-U2 023-01040257070 4.0000
   9900DS-U2 023-14770107080 3.0000
  上述大家需要留意的是:
  ItemNo          PartNo            PerQty
  9900DS-U2 9900-SA-01 2.0000
  
  9900-SA-01 9900-PCB-01 1.0000  9900-SA-01 9900-PCB-02 1.0000  以上三行往下的转换关系,其中PerQty在展开时,会以致乘积其子层用量。
  如:9900DS-U2需要2个9900-SA-01
     而9900-SA-01需要1个9900-PCB-01
     所以，9900DS-U2就需要2个9900-PCB-01不知大家是否明白我的描述，如有不清楚请指出。谢谢！

Answer 1

在理论上和下述例子一样,只不过我这里稍微复杂一些.tbl(Id,ItemNo,PartNo)
数据: 1  A   B
      2  A   B1
      3  A   B2      4  B   C
      5  B1  C1
      6  B2  C2      6  C   D
      7  C1  D1 
      8  C2  D2想得出结果:
      1  A  D
      2  A  D1
      3  A  D2

Answer 2

select a.id,a.itemno,c.PartNo from tbl a,tbl b,tbl c where a.partno = b.itemno and b.partno = c.itemno

Answer 3

CREATE TABLE [#BOM] (
[ItemNo] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[PartNo] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[PerQty] [float] NULL 
) ON [PRIMARY]
GOCREATE TABLE [#T1] (
[ItemNo] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[PartNo] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[PerQty] [float] NULL 
) ON [PRIMARY]
GOCREATE TABLE [#T2] (
[ItemNo] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[PartNo] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[PerQty] [float] NULL 
) ON [PRIMARY]
GOCREATE TABLE [#T3] (
[ItemNo] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[PartNo] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[PerQty] [float] NULL 
) ON [PRIMARY]
GOINSERT INTO #BOM (ItemNo,PartNo,PerQty) VALUES ( '9900DS-U2', '067-017140A', 1 ); INSERT INTO #BOM (ItemNo,PartNo,PerQty) VALUES ( '9900DS-U2', '060-1163', 1 ); INSERT INTO #BOM (ItemNo,PartNo,PerQty) VALUES ( '9900DS-U2', '062-1717', 1 ); INSERT INTO #BOM (ItemNo,PartNo,PerQty) VALUES ( '9900DS-U2', '066-9900DSOJL', 0.25 ); INSERT INTO #BOM (ItemNo,PartNo,PerQty) VALUES ( '9900DS-U2', '070-00980', 1 ); INSERT INTO #BOM (ItemNo,PartNo,PerQty) VALUES ( '9900DS-U2', '068-1017', 1 ); INSERT INTO #BOM (ItemNo,PartNo,PerQty) VALUES ( '9900DS-U2', '9900-SA-01', 2 ); INSERT INTO #BOM (ItemNo,PartNo,PerQty) VALUES ( '9900-SA-01', '003-SD06095', 2 ); INSERT INTO #BOM (ItemNo,PartNo,PerQty) VALUES ( '9900-SA-01', '000-2034-1-2B', 1 ); INSERT INTO #BOM (ItemNo,PartNo,PerQty) VALUES ( '9900-SA-01', '000-2149-1-8C', 1 ); INSERT INTO #BOM (ItemNo,PartNo,PerQty) VALUES ( '9900-SA-01', '000-2059-1-8C', 1 ); INSERT INTO #BOM (ItemNo,PartNo,PerQty) VALUES ( '9900-SA-01', '000-2030-1-8C', 1 ); INSERT INTO #BOM (ItemNo,PartNo,PerQty) VALUES ( '9900-SA-01', '9900-PCB-01', 1 ); INSERT INTO #BOM (ItemNo,PartNo,PerQty) VALUES ( '9900-SA-01', '9900-PCB-02', 1 ); INSERT INTO #BOM (ItemNo,PartNo,PerQty) VALUES ( '9900-PCB-01', '026-0120210410', 2 ); INSERT INTO #BOM (ItemNo,PartNo,PerQty) VALUES ( '9900-PCB-01', '023-12260165050', 1 ); INSERT INTO #BOM (ItemNo,PartNo,PerQty) VALUES ( '9900-PCB-01', '049-F0005', 1 ); INSERT INTO #BOM (ItemNo,PartNo,PerQty) VALUES ( '9900-PCB-01', '023-11050505050', 3 ); INSERT INTO #BOM (ItemNo,PartNo,PerQty) VALUES ( '9900-PCB-01', '026-0680210410', 1 ); INSERT INTO #BOM (ItemNo,PartNo,PerQty) VALUES ( '9900-PCB-01', '036-04060', 1 ); INSERT INTO #BOM (ItemNo,PartNo,PerQty) VALUES ( '9900-PCB-02', '026-0330310410', 1 ); INSERT INTO #BOM (ItemNo,PartNo,PerQty) VALUES ( '9900-PCB-02', '023-01040257070', 4 ); INSERT INTO #BOM (ItemNo,PartNo,PerQty) VALUES ( '9900-PCB-02', '023-14770107080', 3 ); 
INSERT INTO #T2 SELECT * FROM #BOM WHERE ItemNo = '9900DS-U2' AND PartNO IN (SELECT ItemNo FROM #BOM)INSERT INTO #T1 SELECT * FROM #BOM WHERE ItemNo = '9900DS-U2' AND PartNO NOT IN (SELECT ItemNo FROM #BOM)WHILE ( (SELECT Count(*) FROM #T2)>0 )
BEGIN
DELETE FROM #T3
INSERT INTO #T3 SELECT #T2.ItemNO,#BOM.PartNo,#T2.PerQty*#BOM.PerQty AS PerQty FROM #T2 LEFT JOIN #BOM ON #T2.PartNO  = #BOM.ItemNo
INSERT INTO #T1 SELECT * FROM #T3 WHERE PartNO NOT IN (SELECT ItemNo FROM #BOM)
DELETE FROM #T2
INSERT INTO #T2 SELECT * FROM #T3 WHERE PartNO IN (SELECT ItemNo FROM #BOM)

END
SELECT * FROM #BOM
SELECT * FROM #T1
SELECT * FROM #T2
SELECT * FROM #T3DROP TABLE #T1
DROP TABLE #T2
DROP TABLE #T3
DROP TABLE #BOM

Answer 4

得到的结果是：9900DS-U2 , 067-017140A , 1.0
9900DS-U2 , 060-1163 , 1.0
9900DS-U2 , 062-1717 , 1.0
9900DS-U2 , 066-9900DSOJL , 0.25
9900DS-U2 , 070-00980 , 1.0
9900DS-U2 , 068-1017 , 1.0
9900DS-U2 , 003-SD06095 , 4.0
9900DS-U2 , 000-2034-1-2B , 2.0
9900DS-U2 , 000-2149-1-8C , 2.0
9900DS-U2 , 000-2059-1-8C , 2.0
9900DS-U2 , 000-2030-1-8C , 2.0
9900DS-U2 , 026-0120210410 , 4.0
9900DS-U2 , 023-12260165050 , 2.0
9900DS-U2 , 049-F0005 , 2.0
9900DS-U2 , 023-11050505050 , 6.0
9900DS-U2 , 026-0680210410 , 2.0
9900DS-U2 , 036-04060 , 2.0
9900DS-U2 , 026-0330310410 , 2.0
9900DS-U2 , 023-01040257070 , 8.0
9900DS-U2 , 023-14770107080 , 6.0
我写的方法比较笨，希望能看到更好的办法

Answer 5

BOM(ItemNo,PartNo,PerQty)
--创建函数
CREATE FUNCTION dbo.get_leaf_partno
(@itemno nvarchar(200))
RETURNs @t_bom TABLE (ItemNo nvarchar(200),
PartNo nvarchar(200),
PerQty numeric(10,4),
level int)
AS
BEGIN
DECLARE @level  int
DECLARE @r int

SET @level=1
SET @r=0

INSERT INTO @t_bom
SELECT ItemNo,PartNo,PerQty,@level
FROM BOM
WHERE ItemNo=@itemno

SET @r=@@rowcount

WHILE @r>0
BEGIN
INSERT INTO @t_bom
SELECT ItemNo,PartNo,c.PerQty*p.PerQty,@level+1
FROM BOM c,@t_bom p
WHERE c.ItemNo=p.PartNo
AND p.level=@level

SET @r=@@rowcount

DELETE FROM @t_bom
WHERE level=@level
AND PartNo IN (SELECT ItemNo FROM @t_bom WHERE level=@level+1)

SET @level=@level+1
END

INSERT INTO @t_bom
SELECT @itemno,PartNo,sum(PerQty),-1
FROM @t_bom
GROUP BY PartNo

DELETE FROM @t_bom WHERE level<>-1

RETURN
END--调用函数
SELECT ItemNo,PartNo,PerQty FROM dbo.get_leaf_partno('9900DS-U2')

Answer 6

bugchen888(臭虫) 的函数有问题啊，执行过不去

Answer 7

zheng_hz(鸿鹄) 您好:
您的方法估计可以,但您的方法只适用于BOM有三层,
而我的BOM最多有九层.谢谢您的参与.

Answer 8

我没限制层数阿,用while循环的。。

Answer 9

做信息系统的，一定要把程序简化，像BOM表这样的程序，就在程序里写个递规函数就行了

Answer 10

create table BOM(ItemNo varchar(20),PartNo varchar(20),PerQty decimal(10,4))
insert BOM select '9900DS-U2','067-017140A',1.0000 
union all select '9900DS-U2','060-1163', 1.0000 
union all select '9900DS-U2','062-1717', 1.0000 
union all select '9900DS-U2','066-9900DSOJL',.2500 
union all select '9900DS-U2','070-00980', 1.0000 
union all select '9900DS-U2','068-1017', 1.0000 
union all select '9900DS-U2','9900-SA-01',2.0000 --第二层: 
union all select '9900-SA-01','003-SD06095',2.0000 
union all select '9900-SA-01','000-2034-1-2B',1.0000 
union all select '9900-SA-01','000-2149-1-8C',1.0000 
union all select '9900-SA-01','000-2059-1-8C',1.0000 
union all select '9900-SA-01','000-2030-1-8C',1.0000 
union all select '9900-SA-01','9900-PCB-01',1.0000 
union all select '9900-SA-01','9900-PCB-02',1.0000 --第三层: 
union all select '9900-PCB-01','026-0120210410',2.0000 
union all select '9900-PCB-01','023-12260165050',1.0000 
union all select '9900-PCB-01','049-F0005', 1.0000 
union all select '9900-PCB-01','023-11050505050',3.0000 
union all select '9900-PCB-01','026-0680210410',1.0000 
union all select '9900-PCB-01','036-04060', 1.0000 --第三层: 
union all select '9900-PCB-02','026-0330310410',1.0000 
union all select '9900-PCB-02','023-01040257070',4.0000 
union all select '9900-PCB-02','023-14770107080',3.0000 
GO
--------------創建用戶定義函數------------------
CREATE function dbo.f_ItemNo(@ItemNo varchar(1000))
returns varchar(100)
as 
begin
    declare @ret varchar(100) --返回的字符串
    set @ret=@ItemNo
    while exists(select 1 from BOM where PartNo=@ItemNo)
    begin
    select @ItemNo=ItemNo from BOM where PartNo=@ItemNo
     set @ret=@ItemNo
    end
    return @ret
end
GO
--------------創建用戶定義函數------------------
CREATE function dbo.f_PerQty(@ItemNo varchar(1000),@PerQty decimal(10,4))
returns decimal(10,4)
as 
begin   
    declare @ret decimal(10,4)
    set @ret=@PerQty
    while exists(select 1 from BOM where PartNo=@ItemNo)
    begin
    select @ItemNo=ItemNo from BOM where PartNo=@ItemNo
    select @ret=@PerQty*PerQty from BOM where PartNo=@ItemNo
    end
    return @ret
end
GOselect  dbo.f_ItemNo(ItemNo) [ItemNo],PartNo,dbo.f_PerQty(ItemNo ,PerQty) [PerQty] from BOM
where PartNo not in (select ItemNo from BOM )drop function dbo.f_ItemNo
drop function dbo.f_PerQty
drop table BOM/*
ItemNo                                                                                               PartNo               PerQty       
---------------------------------------------------------------------------------------------------- -------------------- ------------ 
9900DS-U2                                                                                            067-017140A          1.0000
9900DS-U2                                                                                            060-1163             1.0000
9900DS-U2                                                                                            062-1717             1.0000
9900DS-U2                                                                                            066-9900DSOJL        .2500
9900DS-U2                                                                                            070-00980            1.0000
9900DS-U2                                                                                            068-1017             1.0000
9900DS-U2                                                                                            003-SD06095          2.0000
9900DS-U2                                                                                            000-2034-1-2B        1.0000
9900DS-U2                                                                                            000-2149-1-8C        1.0000
9900DS-U2                                                                                            000-2059-1-8C        1.0000
9900DS-U2                                                                                            000-2030-1-8C        1.0000
9900DS-U2                                                                                            026-0120210410       4.0000
9900DS-U2                                                                                            023-12260165050      2.0000
9900DS-U2                                                                                            049-F0005            2.0000
9900DS-U2                                                                                            023-11050505050      6.0000
9900DS-U2                                                                                            026-0680210410       2.0000
9900DS-U2                                                                                            036-04060            2.0000
9900DS-U2                                                                                            026-0330310410       2.0000
9900DS-U2                                                                                            023-01040257070      8.0000
9900DS-U2                                                                                            023-14770107080      6.0000
*/

Answer 11

create table BOM(ItemNo varchar(12),PartNo varchar(20),PerQty decimal(10,4))
insert BOM select '9900DS-U2','067-017140A',1.0000 
union all select '9900DS-U2','060-1163', 1.0000 
union all select '9900DS-U2','062-1717', 1.0000 
union all select '9900DS-U2','066-9900DSOJL',.2500 
union all select '9900DS-U2','070-00980', 1.0000 
union all select '9900DS-U2','068-1017', 1.0000 
union all select '9900DS-U2','9900-SA-01',2.0000 --第二层: 
union all select '9900-SA-01','003-SD06095',2.0000 
union all select '9900-SA-01','000-2034-1-2B',1.0000 
union all select '9900-SA-01','000-2149-1-8C',1.0000 
union all select '9900-SA-01','000-2059-1-8C',1.0000 
union all select '9900-SA-01','000-2030-1-8C',1.0000 
union all select '9900-SA-01','9900-PCB-01',1.0000 
union all select '9900-SA-01','9900-PCB-02',1.0000 --第三层: 
union all select '9900-PCB-01','026-0120210410',2.0000 
union all select '9900-PCB-01','023-12260165050',1.0000 
union all select '9900-PCB-01','049-F0005', 1.0000 
union all select '9900-PCB-01','023-11050505050',3.0000 
union all select '9900-PCB-01','026-0680210410',1.0000 
union all select '9900-PCB-01','036-04060', 1.0000 --第三层: 
union all select '9900-PCB-02','026-0330310410',1.0000 
union all select '9900-PCB-02','023-01040257070',4.0000 
union all select '9900-PCB-02','023-14770107080',3.0000 
GO
--------------創建用戶定義函數------------------
--返回最高级父结点
CREATE function dbo.f_ItemNo(@ItemNo varchar(1000))
returns varchar(100)
as 
begin
    declare @ret varchar(100) --返回的字符串
    set @ret=@ItemNo
    while exists(select 1 from BOM where PartNo=@ItemNo)
    begin
    select @ItemNo=ItemNo from BOM where PartNo=@ItemNo
     set @ret=@ItemNo
    end
    return @ret
end
GO
--------------創建用戶定義函數------------------
--返回级别带来的乘积
CREATE function dbo.f_PerQty(@ItemNo varchar(1000),@PerQty decimal(10,4))
returns decimal(10,4)
as 
begin   
    declare @ret decimal(10,4)
    set @ret=@PerQty
    while exists(select 1 from BOM where PartNo=@ItemNo)
    begin
    select @ItemNo=ItemNo from BOM where PartNo=@ItemNo
    select @ret=@PerQty*PerQty from BOM where PartNo=@ItemNo
    end
    return @ret
end
GOselect  dbo.f_ItemNo(ItemNo) [ItemNo],PartNo,dbo.f_PerQty(ItemNo ,PerQty) [PerQty] from BOM
where PartNo not in (select ItemNo from BOM )drop function dbo.f_ItemNo
drop function dbo.f_PerQty
drop table BOM/*
ItemNo                              PartNo               PerQty       
---------------------------------------------------------------------------
9900DS-U2                      067-017140A          1.0000
9900DS-U2                      060-1163             1.0000
9900DS-U2                      062-1717             1.0000
9900DS-U2                      066-9900DSOJL        .2500
9900DS-U2                      070-00980            1.0000
9900DS-U2                      068-1017             1.0000
9900DS-U2                      003-SD06095          2.0000
9900DS-U2                      000-2034-1-2B        1.0000
9900DS-U2                      000-2149-1-8C        1.0000
9900DS-U2                      000-2059-1-8C        1.0000
9900DS-U2                      000-2030-1-8C        1.0000
9900DS-U2                      026-0120210410       4.0000
9900DS-U2                      023-12260165050      2.0000
9900DS-U2                      049-F0005            2.0000
9900DS-U2                      023-11050505050      6.0000
9900DS-U2                      026-0680210410       2.0000
9900DS-U2                      036-04060            2.0000
9900DS-U2                      026-0330310410       2.0000
9900DS-U2                      023-01040257070      8.0000
9900DS-U2                      023-14770107080      6.0000
*/

Answer 12

dutguoyi(新鲜鱼排)  的算法,加到第4层,数量就不对了

Answer 13

create table BOM(ItemNo varchar(20),PartNo varchar(20),PerQty decimal(10,4))
insert BOM select '9900DS-U2','067-017140A',1.0000 
union all select '9900DS-U2','060-1163', 1.0000 
union all select '9900DS-U2','062-1717', 1.0000 
union all select '9900DS-U2','066-9900DSOJL',.2500 
union all select '9900DS-U2','070-00980', 1.0000 
union all select '9900DS-U2','068-1017', 1.0000 
union all select '9900DS-U2','9900-SA-01',2.0000 --第二层: 
union all select '9900-SA-01','003-SD06095',2.0000 
union all select '9900-SA-01','000-2034-1-2B',1.0000 
union all select '9900-SA-01','000-2149-1-8C',1.0000 
union all select '9900-SA-01','000-2059-1-8C',1.0000 
union all select '9900-SA-01','000-2030-1-8C',1.0000 
union all select '9900-SA-01','9900-PCB-01',1.0000 
union all select '9900-SA-01','9900-PCB-02',1.0000 --第三层: 
union all select '9900-PCB-01','026-0120210410',2.0000 
union all select '9900-PCB-01','023-12260165050',1.0000 
union all select '9900-PCB-01','049-F0005', 1.0000 
union all select '9900-PCB-01','023-11050505050',3.0000 
union all select '9900-PCB-01','026-0680210410',1.0000 
union all select '9900-PCB-01','036-04060', 1.0000 --第三层: 
union all select '9900-PCB-02','026-0330310410',1.0000 
union all select '9900-PCB-02','023-01040257070',4.0000 
union all select '9900-PCB-02','023-14770107080',3.0000 
GO-- 计算处理
SET NOCOUNT ON
DECLARE @l INT
SET @l = 1SELECT ItemNo, PartNo, Sum_PerQty = PerQty, Level = @l
INTO [#] FROM BOM A
WHERE NOT EXISTS(
SELECT * FROM BOM
WHERE A.ItemNo = PartNo)
WHILE @@ROWCOUNT > 0
BEGIN
SET @l = @l + 1
INSERT # SELECT B.ItemNo, A.PartNo, Sum_PerQty = B.Sum_PerQty * A.PerQty, @l
FROM BOM A, # B
WHERE A.ItemNo = B.PartNo
AND B.Level = @l - 1
END
SELECT * FROM # A
WHERE NOT EXISTS(
SELECT * FROM BOM
WHERE A.PartNo = ItemNo)
ORDER BY 1, 2
DROP TABLE #
GODROP TABLE BOM

Answer 14

--------------------------------------------------------------
您好，我们是“2006中国杰出数据库工程师评选”活动组委会。
您的帖子已经被我们转载到本次评选官方网站的“专家在线答疑”区。
http://www.bestdba.cn/match_discussion.aspx在那里，进入本次评选复选的90位数据库工程师将与您展开积极的互动。
一方面，他们会为您的问题提供满意的答案，
另一方面，也邀请您为他们投上宝贵的选票。2006-7-8 ~ 2006-7-25日，每天我们将从当天参与"有奖投票"的网友
中抽取3名幸运者，赠送由IBM提供的精美礼品一份！此外，您还可以在“专家在线答疑”区提出新的问题并参与讨论。您的帖子位于：
http://www.bestdba.cn/match_discussion3.aspx?pointid=23&pointid2=1&pointid3=5非常感谢您对本次活动的支持！
--------------------------------------------------------------