表1=BomMaster
欄位：ID,itemcod
這里的itemcode指母件的料號；
表2=BomComponent
欄位：bommaster,itemcode
這里的itemcode指子件的料號；
BomMaster.ID與BomComponent.bommaster關聯；
如何做遞回BOM結構查詢
uj5u.com熱心網友回復：
問過這個問題。別人給我的代碼。要不要。
uj5u.com熱心網友回復：
USE [kji]
GO
/****** Object: UserDefinedFunction [dbo].[f_cid] Script Date: 11/14/2013 16:52:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[f_cid]
(
@BOMNO varchar(100),
@liqty NUMERIC(7,2)
)
returns @t_level table
(
P_CODE varchar(100),
CODE varchar(100),
CBDESC nvarchar(100),
QTY_NEED numeric(7,2),
WASTAGE numeric(7,2),
UNIT nvarchar(20),
--SHS numeric(7,2),
LIQYT numeric(10,3),
level int,level1 varchar(8000)
)
as
begin
declare @level int
declare @level1 int
set @level = 1
set @level1 = '0'
insert into @t_level select P_CODE,CODE,CBDESC,QTY_NEED,(WASTAGE/100)WASTAGE,UNIT,
ceiling((CAST(@liqty AS BIGINT)+(@liqty*(WASTAGE/100)))*QTY_NEED)LIQYT,@level,@level1
from BOMT where BOMT.BOMNO=@BOMNO
while @@ROWCOUNT > 0
begin
set @level = @level + 1
insert into @t_level select a.P_CODE,a.CODE,a.CBDESC,a.QTY_NEED,(a.WASTAGE+b.WASTAGE),a.UNIT,
ceiling((cast(b.LIQYT as bigint) + ceiling(cast (b.LIQYT as bigint)*(a.WASTAGE/100)))*a.QTY_NEED)LIQYT,@level,
case when (select count(*) from BOMT c where a.P_CODE=c.P_CODE and c.CODE>a.CODE and c.CODE<>@BOMNO)=0
then b.level1+'-000'
else b.level1+'-'+right('00'+ltrim((select count(*) from BOMT c where a.P_CODE=c.P_CODE and c.CODE>a.CODE and c.CODE<>@BOMNO)),3)
end
from BOMT a , @t_level b
where a.P_CODE = b.CODE and b.level = @level - 1 and a.CODE<>@BOMNO
end
return
end
uj5u.com熱心網友回復：
還有這是SQL 的問題最好在SQL版里面問uj5u.com熱心網友回復：
sql2005以上,可以用with樹查詢uj5u.com熱心網友回復：
為何分兩個表。一般在一個表中的啊轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/122344.html
標籤：數據庫相關