您好,登錄后才能下訂單哦!
本篇內(nèi)容主要講解“有哪些遍歷BOM表的SQL函數(shù)結(jié)構(gòu)”,感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷,實用性強。下面就讓小編來帶大家學(xué)習(xí)“有哪些遍歷BOM表的SQL函數(shù)結(jié)構(gòu)”吧!
表結(jié)構(gòu)如下:
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函數(shù)結(jié)構(gòu)有哪些
函數(shù)如下:
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
調(diào)用方法如下:
select*fromfn_aaa('a.1')
意思是找出a.1下的所有兒子及孫子。
到此,相信大家對“有哪些遍歷BOM表的SQL函數(shù)結(jié)構(gòu)”有了更深的了解,不妨來實際操作一番吧!這里是億速云網(wǎng)站,更多相關(guān)內(nèi)容可以進入相關(guān)頻道進行查詢,關(guān)注我們,繼續(xù)學(xué)習(xí)!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。