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中表變量的不足