问一个较难的Sql 语句问题了

[dabster1]

part-item type level version qty top item

03114P070098077 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大家有什么办法不,还是这个在前端完成好点了

[arrow_gx]

不明白问题是什么

[wzy_love_sly]

帮顶

[Limpire]

观摩下

[dabster1]

就是输入part_item,然后找出所有top父阶的新版本了 :)

[Limpire]

--> try

select *
  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)

[dabster1]

楼上

这个不行,这样的只有一个成品了,另两个成品抓不出来了
03114P070098077 0 2 D 21.0 03111P070098027 Mattress SHL 8" Base w/packing出貨成品

[Limpire]

比如这几条数据，你要哪些数据

[playwarcraft]

在他的語句上加個and

--> try

select *
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)

[撩天槌]

--> --> (Ben)生成測試數據
 
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'
Go

select 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]

[dabster1]

效果达到了,谢谢,楼上几位,Limpire ,playwarcraft ,还有最后一位