有难度的MSSQL2000 订单毛需求件分别全部展开

Question

MSSQL2000 订单毛需求件分别全部展开(BOM多版本号)
来源订单  (源头数据)
vip_sorderdetail视图 (由sorderdetail表汇总生成的)
materilaid , quantity , bomno
   A       ,  3000    ,  A
   B       ,  4000    ,  BB物料清单
goodsbom主表 ,goodsbomdetail从表 
表关系：goodsbom.billid=goodsbomdetail.billid 相同时表示同一层bom主表goodsbom
单据ID，物料母件ID，版本号，母件数量
billid, materialid,bomno,quantity
1001    , A       , AA  , 1   ---产品A有多个版本
1002    , B       , BB  , 1
1003    , F       , FF  , 1
1004    , A       , AK  , 1   ---产品A有多个版本从表goodsbomdetail
单据ID，明细序号， 物料子件ID,  子件数量 ，子件版本号   
billid,  itemno,    elemgid,   quantity,    bomno
1001    , 1        , B          , 1      , BB    --这里表示物料B还有下阶清单，版本号是BB
1001    , 2        , C          , 1      , ' '
1001    , 3        , D          , 1      , ' '
1002    , 1        , E          , 1      , ' '
1002    , 2        , F          , 2      , FF  
1003    , 1        , G          , 1      , ' '
1003    , 2        , H          , 1      , ' '
1004    , 1        , J          , 1      , ' '
1004    , 2        , K          , 1      , ' '要求结果如下:  根据订单展开毛需求
1/相同物料不合并计算
materialid,bomno,quantity
A         ,  A  ,  3000  
B         ,  B  ,  3000
C         , ''  ,  3000
D         , ''  ,  3000
E         ,''   ,  3000
F         , ''  ,  6000
G         , ''  ,  6000   
H         , ''  ,  60002/相同物料合并计算 (相同材料数量合计)

Answer 1

参考这些内容,自己做.---------------------------
就用树型表,可到N级.参考如下:create table tb(id int, name varchar(10), pid int, px int)
insert into tb values(0 , '栏目分类', 0 , 1)
insert into tb values(1 , '动物' , 0 , 1) 
insert into tb values(2 , '视频' , 0 , 2) 
insert into tb values(3 , '老虎' , 1 , 1) 
insert into tb values(4 , '狮子' , 1 , 2) 
insert into tb values(5 , '搞笑' , 2 , 1) 
go--查询指定节点及其所有子节点的函数 
CREATE FUNCTION f_Cid(@ID int) RETURNS @t_Level TABLE(ID int,Level int) 
AS 
BEGIN 
  DECLARE @Level int 
  SET @Level=1 
  INSERT @t_Level SELECT @ID,@Level 
  WHILE @@ROWCOUNT>0 
  BEGIN 
    SET @Level=@Level+1 
    INSERT @t_Level SELECT a.ID,@Level 
    FROM tb a,@t_Level b 
    WHERE a.PID=b.ID 
    AND b.Level=@Level-1 
  END 
  RETURN 
END 
GO --调用函数查询id = 1及其所有子节点 
SELECT a.* FROM tb a,f_Cid(1) b WHERE a.ID=b.ID 
/*
id          name       pid         px          
----------- ---------- ----------- ----------- 
1           动物         0           1
3           老虎         1           1
4           狮子         1           2
（所影响的行数为 3 行）
*/drop table tb
drop function dbo.f_cid
----------------------------------BOM算法
--产品配件清单查询示例(邹建)
CREATE TABLE Item(ID int,Name varchar(10),Wast decimal(2,2))
INSERT Item SELECT 1,N'A产品',0.01
UNION  ALL  SELECT 2,N'B产品',0.02
UNION  ALL  SELECT 3,N'C产品',0.10
UNION  ALL  SELECT 4,N'D配件',0.15
UNION  ALL  SELECT 5,N'E物料',0.03
UNION  ALL  SELECT 6,N'F物料',0.01
UNION  ALL  SELECT 7,N'G配件',0.02CREATE TABLE Bom(ItemID int,ChildId int)
INSERT Bom SELECT 1,4
UNION  ALL SELECT 1,7   --A产品由D配件和G配件组成
UNION  ALL SELECT 2,1
UNION  ALL SELECT 2,6
UNION  ALL SELECT 2,7   --B产品由F物料及G配件组成
UNION  ALL SELECT 4,5
UNION  ALL SELECT 4,6    --D配件由F物料组成
UNION  ALL SELECT 3,2
UNION  ALL SELECT 3,1    --C产品由A产品和B产品组成
GOCREATE FUNCTION f_Bom(
@ItemIDs varchar(1000), --要查询物料清单及生产量的产品编号列表(逗号分隔)
@Num   int          --要生产的数量
)RETURNS @t TABLE(ItemID int,ChildId int,Nums int,Level int)
AS
BEGIN
    DECLARE @Level int
    SET @Level=1
    INSERT @t SELECT a.ItemID,a.ChildId,ROUND(@Num/(1-b.Wast),0),@Level
    FROM Bom a,Item b
    WHERE a.ChildId=b.ID
        AND CHARINDEX(','+RTRIM(a.ItemID)+',',','+@ItemIDs+',')>0
    WHILE @@ROWCOUNT>0 and @Level<140
    BEGIN
        SET @Level=@Level+1
        INSERT @t SELECT a.ItemID,b.ChildId,ROUND(a.Nums/(1-c.Wast),0),@Level
        FROM @t a,Bom b,Item c
        WHERE a.ChildId=b.ItemID
            AND b.ChildId=c.ID
            AND a.Level=@Level-1
    END
    RETURN
END
GO--调用函数展开产品1、2、3的结构及计算生产10个产品时,各需要多少个配件
SELECT a.ItemID,ItemName=b.Name,
    a.ChildId,ChildName=c.Name,
    a.Nums,a.Level
FROM f_Bom('1,2,3',10) a,Item b,Item c
WHERE a.ItemID=b.ID
    AND a.ChildId=c.ID
ORDER BY a.ItemID,a.Level,a.ChildId/*
ItemID      ItemName   ChildId     ChildName  Nums        Level
----------- ---------- ----------- ---------- ----------- -----------
1           A产品        4           D配件        12          1
1           A产品        7           G配件        10          1
1           A产品        5           E物料        12          2
1           A产品        6           F物料        12          2
2           B产品        1           A产品        10          1
2           B产品        6           F物料        10          1
2           B产品        7           G配件        10          1
2           B产品        4           D配件        12          2
2           B产品        7           G配件        10          2
2           B产品        5           E物料        12          3
2           B产品        6           F物料        12          3
3           C产品        1           A产品        10          1
3           C产品        2           B产品        10          1
3           C产品        1           A产品        10          2
3           C产品        4           D配件        12          2
3           C产品        6           F物料        10          2
3           C产品        7           G配件        10          2
3           C产品        7           G配件        10          2
3           C产品        4           D配件        12          3
3           C产品        5           E物料        12          3
3           C产品        6           F物料        12          3
3           C产品        7           G配件        10          3
3           C产品        5           E物料        12          4
3           C产品        6           F物料        12          4(24 row(s) affected)
*/
drop table item
drop table bom
drop function f_Bom-------------------------------------------------------------
就用树型表,可到N级.参考如下:create table tb(id int, name varchar(10), pid int, px int)
insert into tb values(0 , '栏目分类', 0 , 1)
insert into tb values(1 , '动物' , 0 , 1) 
insert into tb values(2 , '视频' , 0 , 2) 
insert into tb values(3 , '老虎' , 1 , 1) 
insert into tb values(4 , '狮子' , 1 , 2) 
insert into tb values(5 , '搞笑' , 2 , 1) 
go--查询指定节点及其所有子节点的函数 
CREATE FUNCTION f_Cid(@ID int) RETURNS @t_Level TABLE(ID int,Level int) 
AS 
BEGIN 
  DECLARE @Level int 
  SET @Level=1 
  INSERT @t_Level SELECT @ID,@Level 
  WHILE @@ROWCOUNT>0 
  BEGIN 
    SET @Level=@Level+1 
    INSERT @t_Level SELECT a.ID,@Level 
    FROM tb a,@t_Level b 
    WHERE a.PID=b.ID 
    AND b.Level=@Level-1 
  END 
  RETURN 
END 
GO --调用函数查询id = 1及其所有子节点 
SELECT a.* FROM tb a,f_Cid(1) b WHERE a.ID=b.ID 
/*
id          name       pid         px          
----------- ---------- ----------- ----------- 
1           动物         0           1
3           老虎         1           1
4           狮子         1           2
（所影响的行数为 3 行）
*/drop table tb
drop function dbo.f_cid
-----------------------------------------------------
--测试数据
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
    DECLARE @Level int
    SET @Level=1
    INSERT @t_Level SELECT @ID,@Level
    WHILE @@ROWCOUNT>0
    BEGIN
        SET @Level=@Level+1
        INSERT @t_Level SELECT a.ID,@Level
        FROM tb a,@t_Level b
        WHERE a.PID=b.ID
            AND b.Level=@Level-1
    END
    RETURN
END
GO--调用函数查询002及其所有子节点
SELECT a.*
FROM tb a,f_Cid('002') b
WHERE a.ID=b.ID
/*--结果
ID   PID  Name       
------ ------- ---------- 
002  001  烟台市
004  002  招远市
--*/--测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
    SET @Level=@Level+1
    INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
    FROM @t a,@t_Level b
    WHERE a.PID=b.ID
        AND b.Level=@Level-1
END--显示结果
SELECT SPACE(b.Level*2)+'|--'+a.Name
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
|--山东省
  |--烟台市
    |--招远市
  |--青岛市
|--四会市
  |--清远市
    |--小分市
--*/
-----------------------------------------

Answer 2

谢谢你的回复,小弟刚开始学有点不懂.
这个是(BOM多版本号)