遍历BOM表的SQL函数_SQL Server_数据库_程序员俱乐部

SQL函数的种类很多，实现的功能也不太一样。下面为您介绍的是用于遍历BOM表的SQL函数，希望可以让您对SQL函数有更多的了解。

表结构如下:
ptype subptype amount
a  a.120
a  a.2 15
a  a.3 10
a. 1 a.1.1 20
a.1a.1.2  15
a.1 a.1.330
a.2 a.2.110
a.2 a.2.2 20
a.1.1 a.1.1.1 45
a.1.1 a.1.1.2 15
a.2.1 a.2.1.1 20
a.2.2 a.2.2.1 13

create table matgroup(parentgroup varchar(50),childgroup varchar(50), mount float)  
 
insert into matgroup   
select 'a',  'a.1',20  
union select 'a',  'a.2', 15  
union select 'a',  'a.3', 10  
union select 'a.1', 'a.1.1', 20  
union select 'a.1','a.1.2',  15  
union select 'a.1', 'a.1.3',30  
union select 'a.2', 'a.2.1',10  
union select 'a.2', 'a.2.2', 20  
union select 'a.1.1', 'a.1.1.1', 45  
union select 'a.1.1', 'a.1.1.2', 15  
union select 'a.2.1' ,'a.2.1.1', 20  
union select 'a.2.2', 'a.2.2.1', 13  

函数如下：

create FUNCTION fn_aaa (@matgroup varchar(50),@mount int )  
RETURNS @retPLExpand TABLE (parentgroup varchar(50),childgroup varchar(50), mount float)  
 
AS  
BEGIN  
DECLARE @RowsAdded int  
declare @PLExpand Table (parentgroup varchar(50),childgroup varchar(50), mount float,processed tinyint default(0))  
 
INSERT @PLExpand  
 SELECT b.parentgroup,b.childgroup, @mount*b.mount, 0  
 FROM matgroup b   
 WHERE b.parentgroup=@matgroup  
SET @RowsAdded = @@rowcount  
 
-- While new employees were added in the previous iteration  
 
WHILE @RowsAdded > 0  
 
BEGIN  
/*Mark all employee records whose direct reports are going to be   
found in this iteration with processed=1.*/  
UPDATE @PLExpand  
SET processed = 1 
WHERE processed = 0 
 
-- Insert employees who report to employees marked 1.  
INSERT @PLExpand  
SELECT a.parentgroup,a.childgroup,a.mount*b.mount , 0  
FROM matgroup a inner join @PLExpand b on a.parentgroup=b.childgroup  
  where b.processed = 1 
 
SET @RowsAdded = @@rowcount  
/*Mark all employee records whose direct reports have been found  
in this iteration.*/  
 
UPDATE @PLExpand  
SET processed = 2 
WHERE processed = 1 
END  
 
-- copy to the result of the function the required columns  
INSERT @retPLExpand  
SELECT parentgroup,childgroup,mount  
FROM @PLExpand  
RETURN  
END  

调用方法如下:
select * from fn_aaa('a.1')
意思是找出a.1下的所有儿子及孙子.