日韩无码专区无码一级三级片|91人人爱网站中日韩无码电影|厨房大战丰满熟妇|AV高清无码在线免费观看|另类AV日韩少妇熟女|中文日本大黄一级黄色片|色情在线视频免费|亚洲成人特黄a片|黄片wwwav色图欧美|欧亚乱色一区二区三区

SQL函數(shù)可以實現(xiàn)諸多的功能，下面就將為您介紹SQL函數(shù)遍歷BOM表的方法，供您參考，希望對您學(xué)SQL函數(shù)的使用能夠所有幫助。

表結(jié)構(gòu)如下:
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#

函數(shù)如下:
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

調(diào)用方法如下:
select * from fn_aaa('a.1')
意思是找出a.1下的所有兒子及孫子.

【編輯推薦】

判斷閏年的SQL函數(shù)

巧用SQL函數(shù)讓公歷變農(nóng)歷

詳解SQL中的DATALENGTH函數(shù)

基于時間SQL函數(shù)詳解

SQL中表變量的不足

網(wǎng)站標(biāo)題：sql函數(shù)遍歷BOM表的方法
鏈接地址：http://www.5511xx.com/article/cdodijp.html