with T as (
select child,parent,
MAX(version) KEEP(DENSE_RANK LAST ORDER BY version,iteration,ROWID) version,
MAX(iteration) KEEP(DENSE_RANK LAST ORDER BY version,iteration,ROWID) iteration
from bomlist
group by parent,child
)
select child,parent,level,version,iteration
from T
start with parent='parentNumber'
connect by prior child=parent
select child,parent,
MAX(version) KEEP(DENSE_RANK LAST ORDER BY version,iteration,ROWID) version,
MAX(iteration) KEEP(DENSE_RANK LAST ORDER BY version,iteration,ROWID) iteration
from bomlist
group by parent,child
)
select child,parent,level,version,iteration
from T
start with parent='parentNumber'
connect by prior child=parent
解决方案 »
- oracle 函数的一些问题
- mysql数据库迁移到oracle数据时,表名及字段修改
- 两个关于oracle的问题,非常简单,但是我是菜鸟
- oracle enterprise manager 10g
- 【江湖告急】怎么根据条件排序!!!
- 如何将本地的图片声音文件写入oracle数据库的blob字段中
- sql------------------>Oracle
- Oracle中的自动增量值如何恢复使之同步??急!在线等待!一定给分!
- 本人有几个疑问。明天要去面试了。各位帮忙啊。
- 求解一个sql,这种判断怎么写啊?求大神帮助
- oracle 带参数存储过程无法执行
- PHP和oracle数据库连接后,用户名和密码无效是咋回事?能正常登录sql\PLUS呀
感谢版主回覆,虽然我想要取得的是下阶组件或原料的最高版本,说明如下:
原数据:
成品E.1
组件甲A.1
原料一A.3
原料一B.1
原料二C.2
组件甲A.2
原料一A.3
原料一B.1
原料三A.4
组件乙B.4
原料四A.2
原料四A.2
原料五F.1结果(同一层只留每个组件或原料的最高版本):
成品E.1
组件甲A.2
原料一B.1
原料三A.4
组件乙B.4
原料四A.2
原料四A.2
原料五F.1但是感谢版主提供的思路,所以我先把表数据整成组件或原料的最高版本版序的临时表T,之后再组上下层:with T as (
select a.partno,a.constituentpartno,a.version,a.iteration,a.plocation,a.phantom,a.isassembly,constituentpartqty,constituentpartunit,drawing,changenumber,modifier,effectivedate
from purgebomlist a,(
select partno,version,max(iteration) as iteration
from(
select a.partno,b.version,a.iteration
from purgebomlist a,(
select a.partno,b.data as version
from(
select partno,MAX(data2) as version
from (select a.partno,a.version,b.data2 from purgebomlist a,sys_ctl b where b.sys='PVERSION' and b.ctl_id='PART' and ctl_type='PART_VERSION' and a.version=b.data)
group by partno) a,sys_ctl b
where b.sys='PVERSION' and b.ctl_id='PART' and ctl_type='PART_VERSION' and a.version=b.data2) b
where a.partno=b.partno and a.version=b.version
)
group by partno,version
) b
where a.partno=b.partno and a.version=b.version and a.iteration=b.iteration
)
select constituentpartno,partno,version,iteration,level,plocation,phantom,isassembly,constituentpartqty,constituentpartunit,drawing,changenumber,modifier,effectivedate
from T
start with partno='最上层成品' and version='E' and iteration='1'
connect by prior constituentpartno=partno
select a.partno,a.version,b.data2 from purgebomlist a,sys_ctl b where b.sys='PVERSION' and b.ctl_id='PART' and ctl_type='PART_VERSION' and a.version=b.data以上的表sys_ctl是由A到ZZ的一个排序比较,就是把A到ZZ转换成001到702来比对版本大小