BOM表-SQL-示例

转自：http://topic.csdn.net/u/20090612/16/3ca0b3ef-aa21-41da-bdd2-58baa7d21a26.html

CREATE TABLE BOM(PID INT,ID INT) 
 INSERT INTO BOM SELECT 801,101 
 INSERT INTO BOM SELECT 801,102 
 INSERT INTO BOM SELECT 801,103 
 INSERT INTO BOM SELECT 801,601 
 INSERT INTO BOM SELECT 601,101 
 INSERT INTO BOM SELECT 601,105 
 INSERT INTO BOM SELECT 601,501 
 INSERT INTO BOM SELECT 501,106 
 INSERT INTO BOM SELECT 501,121 
 GO 
  
 CREATE FUNCTION F_GETROOT(@PID INT) 
 RETURNS INT 
 AS 
 BEGIN 
     DECLARE @ID INT 
     WHILE EXISTS(SELECT 1 FROM BOM WHERE ID=@PID) 
     BEGIN 
         SET @ID=@PID 
         SELECT @PID=PID FROM BOM WHERE ID=@ID 
     END 
     RETURN @PID 
 END 
 GO 
  
 SELECT PID=DBO.F_GETROOT(PID),ID FROM BOM 
 GO 
  
 /* 
 PID         ID 
 ----------- -----------  
 801         101 
 801         102 
 801         103 
 801         601 
 801         101 
 801         105 
 801         501 
 801         106 
 801         121 
 */ 
  
  
 DROP FUNCTION F_GETROOT 
 DROP TABLE BOM 
 GO 
  
  
 --生成测试数据 
 create table BOM_1(Item int,bom_head varchar(20),bom_child varchar(20),number int,products_attribute  varchar(20)) 
 insert into BOM_1 select 1 ,'A' ,'A1',1,'采购' 
 insert into BOM_1 select 2 ,'A' ,'A2',2,'生产' 
 insert into BOM_1 select 3 ,'A2','A3',3,'生产' 
 insert into BOM_1 select 4 ,'A2','A4',2,'采购' 
 insert into BOM_1 select 5 ,'A3','A5',2,'采购' 
 insert into BOM_1 select 6 ,'A3','A6',1,'采购' 
 insert into BOM_1 select 7 ,'B' ,'B1',1,'采购' 
 insert into BOM_1 select 8 ,'B' ,'B2',2,'生产' 
 insert into BOM_1 select 9 ,'B2','B3',3,'生产' 
 insert into BOM_1 select 10,'B2','B4',2,'采购' 
 insert into BOM_1 select 11,'B3','B5',2,'采购' 
 insert into BOM_1 select 12,'B3','B6',2,'采购' 
 go 
     
  
 --创建用户定义函数，用于取每个父节点下子节点的采购配置信息 
 create function f_stock(@bom_head varchar(20)) 
 returns @t table(bom varchar(20),number int) 
 as 
 begin  
     declare @level int 
     declare @a table(bom varchar(20),number int,products_attribute varchar(20),[level] int) 
     set @level=1 
  
     if exists(select 1 from BOM_1 where bom_head=@bom_head)     
     insert into @a  
     select bom_child,number,products_attribute,@level  
     from BOM_1  
     where bom_head=@bom_head 
      
     while exists(select 1 from @a where [level]=@level and products_attribute='生产') 
     begin 
         set @level=@level+1 
         insert into @a(bom,number,products_attribute,[level]) 
         select a.bom_child,a.number,a.products_attribute,@level  
         from BOM_1 a,@a b 
         where a.bom_head=b.bom and b.[level]=@level-1 
     end 
      
     insert into @t(bom,number) select bom,number from @a where products_attribute='采购' 
     return 
 end 
 go 
  
  
 --执行调用，取父节点'A'一个标准配置分解的采购信息及数量 
 select * from dbo.f_stock('A') 
  
  
 --生成测试数据 
 create table BOM(ID INT,PID INT,MSG VARCHAR(1000)) 
 insert into BOM select 1,0,NULL 
 insert into BOM select 2,1,NULL 
 insert into BOM select 3,1,NULL 
 insert into BOM select 4,2,NULL 
 insert into BOM select 5,3,NULL 
 insert into BOM select 6,5,NULL 
 insert into BOM select 7,6,NULL 
 go 
  
 --创建用户定义函数用于取每个父节点下子节点的采购配置信息 
 create function f_getChild(@ID VARCHAR(10)) 
 returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT) 
 as 
 begin 
     declare @i int 
     set @i = 1 
     insert into @t select ID,PID,@i from BOM where PID = @ID 
      
     while @@rowcount <>0 
     begin 
         set @i = @i + 1 
          
         insert into @t  
         select  
             a.ID,a.PID,@i  
         from  
             BOM a,@t b  
         where  
             a.PID=b.ID and b.Level = @i-1 
     end 
     return 
 end 
 go 
  
 --执行查询 
 select ID from dbo.f_getChild(3) 
 go 
  
 --输出结果 
 /* 
 ID 
 ---- 
 5 
 6 
 7 
 */ 
  
 --删除测试数据 
 drop function f_getChild 
 drop table BOM 
  
  
 创建用户定义函数,每个子节点de父节点的信息 
  
  
 --生成测试数据 
 create table BOM(ID int,parentID int,sClassName varchar(10)) 
 insert into BOM values(1,0,'1111'      ) 
 insert into BOM values(2,1,'1111_1'    ) 
 insert into BOM values(3,2,'1111-1-1'  ) 
 insert into BOM values(4,3,'1111-1-1-1')  
 insert into BOM values(5,1,'1111-2'    ) 
  
 go 
  
 --创建用户定义函数,每个子节点de父节点的信息 
 create function f_getParent(@ID int) 
 returns varchar(40) 
 as 
 begin 
     declare @ret varchar(40) 
  
     while exists(select 1 from BOM where ID=@ID and parentID <>0) 
     begin 
         select @ID=b.ID,@ret=','+rtrim(b.ID)+isnull(@ret,'') 
         from 
             BOM a,BOM b 
         where 
             a.ID=@ID and b.ID=a.parentID 
     end 
      
     set @ret=stuff(@ret,1,1,'') 
     return @ret 
 end 
 go 
  
 --执行查询 
 select ID,isnull(dbo.f_getParent(ID),'') as parentID from BOM 
 go 
  
 --输出结果 
 /* 
 ID          parentID                                  
 ----------- ----------------------------------------  
 1            
 2           1 
 3           1,2 
 4           1,2,3 
 5           1    
 */ 
  
 --删除测试数据 
 drop function f_getParent 
 drop table BOM 
 go