part-item type level version qty top item03114P070098077 0 2 A 21.0 03111P070098027 Mattress SHL 8" Base w/packing出貨成品
03114P070098077 0 2 D 21.0 03111P070098027 Mattress SHL 8" Base w/packing出貨成品
03114P070098077 0 2 C 21.0 03111P070098027 Mattress SHL 8" Base w/packing出貨成品
03114P070098077 0 2 B 21.0 03111P070098027 Mattress SHL 8" Base w/packing出貨成品
03114P070098077 0 2 A 21.0 03111P070098057 Mattress Community Plus no cover w/packing 出貨成品
03114P070098077 0 2 A 21.0 03111P070098063 Mattress Plus (SB) w/201SN pump 出貨成品
03114P070098077 0 2 B 21.0 03111P070098063 Mattress Plus (SB) w/201SN pump 出貨成品
03114P070098077 0 2 C 21.0 03111P070098063 Mattress Plus (SB) w/201SN pump 出貨成品
03114P070098077 0 1 A 21.0 03117100098009 Mattress Community Plus (SB)
03114P070098077 0 1 A 21.0 03117100098040 Mattress GenieCare 8" w/packing (原物料) 这是我从bom里抓出来的,就是输入part_item ,就直接查找到他的top父阶,但是父阶有很多个版本了,我想取最新的版本 ,也就是实际上我 03111P070098027我只要D版,依次类推,所有top父阶的最新版,注意不同输入part_item 取出的top父阶肯定不一样了
select *
from bom_ex
where part_item='03114P070098077'
这个是原始SQL大家有什么办法不,还是这个在前端完成好点了
from bom_ex as t
where part_item='03114P070098077' and not exists (select 1 from bom_ex where part_item=t.part_item and version>t.version)
03114P070098077 0 2 D 21.0 03111P070098027 Mattress SHL 8" Base w/packing出貨成品
from bom_ex as t
where part_item='03114P070098077'
and not exists (select 1 from bom_ex
where part_item=t.part_item
and top_item=t.top_item
and version>t.version)
if not object_id('Tempdb..#T1') is null
drop table #T
Go
Create table #T([part_item] nvarchar(15),[type] int,[level] int,[version] nvarchar(1),[qty] decimal(18,1),[top] nvarchar(15),[item] nvarchar(41))
Insert #T
select '03114P070098077',0,2,'A',21.0,'03111P070098027','MattressSHL8Basew/packing出貨成品' union all
select '03114P070098077',0,2,'D',21.0,'03111P070098027','MattressSH8Basew/packing出貨成品' union all
select '03114P070098077',0,2,'C',21.0,'03111P070098027','MattressSHL8Basew/packing出貨成品' union all
select '03114P070098077',0,2,'B',21.0,'03111P070098027','MattressSHL8Basew/packing出貨成品' union all
select '03114P070098077',0,2,'A',21.0,'03111P070098057','MattressCommunityPlusnocoverw/packing出貨成品' union all
select '03114P070098077',0,2,'A',21.0,'03111P070098063','MattressPlus(SB)w/201SNpump貨成品' union all
select '03114P070098077',0,2,'B',21.0,'03111P070098063','MattressPlus(SB)w/201SNpump出貨成品' union all
select '03114P070098077',0,2,'C',21.0,'03111P070098063','MattressPlus(SB)w/201SNpump出貨成品' union all
select '03114P070098077',0,1,'A',21.0,'03117100098009','MattressCommunityPlusSB)' union all
select '03114P070098077',0,1,'A',21.0,'03117100098040','MattressGenieCare8w/packing'
Goselect b.* from
( select part_item,max([version])version,[top]
from #T a
where part_item='03114P070098077' group by part_item,[top]
)a
left join #T b on a.part_item=b.part_item and a.version=b.version and a.[top]=b.[top]