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下的所有儿子及孙子.
【编辑推荐】
