Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the SQL function structure of traversing the BOM table", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what are the SQL function structures for traversing BOM tables"?

The table structure is as follows:

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' 20 UnionselectSelectAccording to 45 UnionselectSelectAccording.1.1.1Zhenzhongyuan.1.1.2Zhone15UnionselectLight.2.1Zongzhonga.2.1.1Zhone20unionselectLight.2.21Zhone13

What are the SQL function structures of traversing the BOM table

Functions are as follows:

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 > 0BEGINGR 0FROMmatgroupainnerjoinexamples PLExpandbona.parentgrouproomb.childgroupwhere b.processedbones 1SETRANERS RowsAddedExpandSETprocessedholders PLExpandSETprocessedholders 2WHEREprocessedholders 1ENDUTERT copytotheresultofthefunctiontherequiredcolumnsINSERTretPLExpandSECTparentgroupparentgroupparentgroupparpparentgroup

The calling method is as follows:

Select*fromfn_aaa ('a.1')

It means to find out all the sons and grandchildren under a.1.

At this point, I believe you have a deeper understanding of "what is the SQL function structure of traversing the BOM table?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.