iT邦幫忙

0

求ms sql 語法, 如何SELECT 出ERP 中的多階材料用量清單

BOM表中有主件品號,元件品號
A主件底下有
BCD元件
但BCD卻又是別人的主件
請問要如何select出全部到最低階,如第三張所顯示,謝謝。
https://ithelp.ithome.com.tw/upload/images/20190514/20103088szcaHD1NxL.jpg
https://ithelp.ithome.com.tw/upload/images/20190514/20103088IWrzhbxz0b.jpg
https://ithelp.ithome.com.tw/upload/images/20190514/20103088nGQH8qorPU.jpg

0
暐翰
iT邦大師 1 級 ‧ 2019-05-14 22:27:26
最佳解答

BOM表中有主件品號,元件品號
TOP_BOM_ID = BOM_ID

可以使用CTE+inner join遞迴
優點 : 可以使用索引

declare @主件 nvarchar(20) = 'A'; --要搜尋的BOMID
with cte as (
    select [主件],[元件],1 as level,cast([主件]+[元件] as nvarchar(max)) as Ord
    from T
    where [主件] = @主件
    union all
    select T1.[主件],T1.[元件],T2.level + 1 as level,cast(T2.ord + T1.[元件] as nvarchar(max)) as ord
    from T T1
    inner join cte T2 on T1.主件 = T2.元件
)
select  
'|'+(REPLICATE(' |', level-1) + cast(level AS nvarchar(5)))+'|' 階層
,[主件],[元件]
from cte
order by ord

結果

線上測試連結

測試DDL

CREATE TABLE T
	([主件] nvarchar(1), [元件] nvarchar(1))
;
	
INSERT INTO T
	([主件], [元件])
VALUES
	('A', 'B'),
	('A', 'C'),
	('A', 'D'),
	('B', 'E'),
	('B', 'F'),
	('B', 'G')
;

新問題

如果是要一次搜尋所有主件呢,搜尋結果都在同一張表單??

使用DENSE_RANK來分組BOM並去掉where bom_id篩選

with cte as (
	select DENSE_RANK() over (order by [主件]) GroupID,*
	from #T
)
,cte2 as (
    select GroupID,[主件],[元件],1 as level,cast([主件]+[元件] as nvarchar(max)) as Ord
    from cte
    union all
    select T2.GroupID,T1.[主件],T1.[元件],T2.level + 1 as level,cast(T2.ord + T1.[元件] as nvarchar(max)) as ord
    from cte T1
    inner join cte2 T2 on T1.主件 = T2.元件
)
select  
	GroupID as 第幾組,'|'+(REPLICATE(' |', level-1) + cast(level AS nvarchar(5)))+'|' 階層
	,[主件],[元件]
from cte2
order by GroupID,ord

線上測試連結

看更多先前的回應...收起先前的回應...
z22344566 iT邦新手 5 級 ‧ 2019-05-15 09:00:27 檢舉

BOM的ID
就是主件...

暐翰 iT邦大師 1 級 ‧ 2019-05-15 12:27:33 檢舉

明白,我更新回答了 z22344566

z22344566 iT邦新手 5 級 ‧ 2019-05-15 13:07:25 檢舉

請問
如果是要一次搜尋所有主件呢,搜尋結果都在同一張表單??
謝謝!!

暐翰 iT邦大師 1 級 ‧ 2019-05-15 14:46:29 檢舉

z22344566 我更新回答了

z22344566 iT邦新手 5 級 ‧ 2019-05-16 09:17:36 檢舉

拜小弟一跪,非常謝謝。

暐翰 iT邦大師 1 級 ‧ 2019-05-16 13:25:06 檢舉

z22344566
/images/emoticon/emoticon12.gif
假如有解決問題 麻煩設為最佳答案 方便後人查閱

z22344566 iT邦新手 5 級 ‧ 2019-05-20 16:29:09 檢舉

還在測試中
謝謝幫忙~~~

0
舜~
iT邦研究生 3 級 ‧ 2019-05-14 15:51:40
z22344566 iT邦新手 5 級 ‧ 2019-05-15 09:07:38 檢舉

謝謝您!!

0
dscwferp
iT邦好手 1 級 ‧ 2019-05-14 17:26:49

很久以前在網路上找到的

新增 function

CREATE function [dbo].[bomtree](@MD001 CHAR(20))
returns @t table(MD001 char(20),MD002 char(4),MD003 char(20),MD004 char(4),MD006 numeric(11,3),MD007 numeric(4,0),
                ITEM int,sort Nvarchar(1000)  ) 
as
begin
     declare @ITEM int
     set @ITEM=1
     insert into @t
     select MD001,MD002,MD003,MD004,MD006,MD007,@ITEM,MD001+MD003
     from BOMMD
     where MD001=@MD001
     while @@rowcount>0
     begin
         set @ITEM=@ITEM+1
         insert @t
         select a.MD001,a.MD002,a.MD003,a.MD004,a.MD006*b.MD006,a.MD007,@ITEM,b.sort+a.MD003
         from  BOMMD a ,@t b
         where  a.MD001=b.MD003 
                and b.ITEM=@ITEM-1   
    end
return
end 
GO

使用方式
select * from bomtree('主件品號') order by sort

以上!
請依貴司ERP來修正
希望能幫您一點忙!

z22344566 iT邦新手 5 級 ‧ 2019-05-15 09:07:32 檢舉

謝謝您!!這個有符合需求....謝謝

但如果要全部SELECT出來
不是只找單一的主件品號呢?

dscwferp iT邦好手 1 級 ‧ 2019-05-15 12:15:52 檢舉

很久以前在網路上找到的

Declare @MC001 varchar(30)
DECLARE ITEM CURSOR FOR
    SELECT MC001 
	FROM BOMMC
	order by MC001
OPEN ITEM
FETCH NEXT FROM ITEM
INTO @MC001
WHILE   @@FETCH_STATUS = 0
BEGIN
	SELECT * FROM bomtree(@MC001) order by sort
	FETCH NEXT FROM ITEM
	INTO @MC001
END
CLOSE ITEM
DEALLOCATE ITEM

以上!
請依貴司ERP來修正
希望能幫您一點忙!

z22344566 iT邦新手 5 級 ‧ 2019-05-15 13:07:03 檢舉

這表單都是分開的
有辦法能夠全部在同一張表單嗎

這些語法都很陌生沒使用過.....
但是謝謝提供,有學習的方向謝謝!!

我要發表回答

立即登入回答