建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營(yíng)銷解決方案
遍歷BOM表的SQL函數(shù)
SQL函數(shù)的種類很多，實(shí)現(xiàn)的功能也不太一樣。下面為您介紹的是用于遍歷BOM表的SQL函數(shù)，希望可以讓您對(duì)SQL函數(shù)有更多的了解。
成都創(chuàng)新互聯(lián)歡迎咨詢：18980820575，為您提供成都網(wǎng)站建設(shè)網(wǎng)頁(yè)設(shè)計(jì)及定制高端網(wǎng)站建設(shè)服務(wù)，成都創(chuàng)新互聯(lián)網(wǎng)頁(yè)制作領(lǐng)域10多年，包括成都混凝土攪拌站等多個(gè)行業(yè)擁有豐富的網(wǎng)站維護(hù)經(jīng)驗(yàn)，選擇成都創(chuàng)新互聯(lián)，為企業(yè)錦上添花。
表結(jié)構(gòu)如下:
ptype subptype amount
a a.120
a a.2 15
a a.3 10
a. 1 a.1.1 20
a.1a.1.2 15
a.1 a.1.330
a.2 a.2.110
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
函數(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
- -- 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下的所有兒子及孫子.
【編輯推薦】
動(dòng)態(tài)sql中使用臨時(shí)表的實(shí)例
Oracle存儲(chǔ)過(guò)程使用動(dòng)態(tài)SQL
SQL Server刪除視圖的兩種方法
SQL Server視圖的使用
sql server表格變量的用法
名稱欄目：遍歷BOM表的SQL函數(shù)
轉(zhuǎn)載源于：http://m.fisionsoft.com.cn/article/cccosdd.html
