SQL函数的种类很多,实现的功能也不太一样。下面学步园小编来讲解下遍历BOM表的SQL函数结构有哪些?
遍历BOM表的SQL函数结构有哪些
表结构如下:
ptypesubptypeamount
aa.120
aa.215
aa.310
a.1a.1.120
a.1a.1.215
a.1a.1.330
a.2a.2.110
a.2a.2.220
a.1.1a.1.1.145
a.1.1a.1.1.215
a.2.1a.2.1.120
a.2.2a.2.2.113
createtablematgroup(parentgroupvarchar(50),childgroupvarchar(50),mountfloat)insertintomatgroupselect'a','a.1',20unionselect'a','a.2',15unionselect'a','a.3',10unionselect'a.1','a.1.1',20unionselect'a.1','a.1.2',15unionselect'a.1','a.1.3',30unionselect'a.2','a.2.1',10unionselect'a.2','a.2.2',20unionselect'a.1.1','a.1.1.1',45unionselect'a.1.1','a.1.1.2',15unionselect'a.2.1','a.2.1.1',20unionselect'a.2.2','a.2.2.1',13
遍历BOM表的SQL函数结构有哪些
函数如下:
createFUNCTIONfn_aaa(@matgroupvarchar(50),@mountint)RETURNS@retPLExpandTABLE(parentgroupvarchar(50),childgroupvarchar(50),mountfloat)ASBEGINDECLARE@RowsAddedintdeclare@PLExpandTable(parentgroupvarchar(50),childgroupvarchar(50),mountfloat,processedtinyintdefault(0))INSERT@PLExpandSELECTb.parentgroup,b.childgroup,@mount*b.mount,0FROMmatgroupbWHEREb.parentgroup=@matgroupSET@RowsAdded=@@rowcount--WhilenewemployeeswereaddedinthepreviousiterationWHILE@RowsAdded>0BEGIN/*Markallemployeerecordswhosedirectreportsaregoingtobefoundinthisiterationwithprocessed=1.*/UPDATE@PLExpandSETprocessed=1WHEREprocessed=0--Insertemployeeswhoreporttoemployeesmarked1.INSERT@PLExpandSELECTa.parentgroup,a.childgroup,a.mount*b.mount,0FROMmatgroupainnerjoin@PLExpandbona.parentgroup=b.childgroupwhereb.processed=1SET@RowsAdded=@@rowcount/*Markallemployeerecordswhosedirectreportshavebeenfoundinthisiteration.*/UPDATE@PLExpandSETprocessed=2WHEREprocessed=1END--copytotheresultofthefunctiontherequiredcolumnsINSERT@retPLExpandSELECTparentgroup,childgroup,mountFROM@PLExpandRETURNEND
调用方法如下:
select*fromfn_aaa('a.1')
意思是找出a.1下的所有儿子及孙子。
以上就是关于“遍历BOM表的SQL函数结构有哪些”的内容,希望对大家有用。更多资讯请关注学步园。学步园,您学习IT技术的优质平台!