sql函数遍历BOM表的方法

SQL函数可以实现诸多的功能，下面就将为您介绍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#p#

函数如下:
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#p#

-- 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下的所有儿子及孙子.

【编辑推荐】

判断闰年的SQL函数

巧用SQL函数让公历变农历

详解SQL中的DATALENGTH函数

基于时间SQL函数详解

SQL中表变量的不足