sql函数遍历BOM表的方法(1)
发布时间：2010-09-09 13:32 来源：未知
SQL函数可以实现诸多的功能，接下来的SQL server内容就将为您介绍SQL函数遍历BOM表的方法，供您参考，希望对您学SQL函数的使用能够所有帮助。
表结构如下:
函数如下:
-- copy to the result of the function the required columns
SQL函数可以实现诸多的功能，接下来的SQL server内容就将为您介绍SQL函数遍历BOM表的方法，供您参考，希望对您学SQL函数的使用能够所有帮助。
表结构如下:
ptype subptype amount
a a.1 20
a a.2 15
a a.3 10
a. 1 a.1.1 20
a.1 a.1.2 15
a.1 a.1.3 30
a.2 a.2.1 10
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下的所有儿子及孙子.
