新人刚接触oracle,遇到难题希望高手指教!
在一张表中有订单号和运单号两个栏位,格式如下:
ordercode shiptocode
- - - - - - - - - - - - - - - - - -
AA975773.01 BB00001
AA975773.02 BB00002
AA975773.03 BB00003
AA975773.04 BB00004
AA975773.04 BB00005
AA231431.01 BB00011
AA231431.02 BB00012
AA231431.03 BB00013
AA231431.04 BB00014
…… ………
现希望选出这样的一个新栏位,如果ordercode是主订单,即以.01结尾,则新栏位等于shiptocode,如果是副订单,即以其他数字结尾,则新栏位等于主订单的shiptocode。即选出如下格式:
ordercode shiptocode new
- - - - - - - - - - - - - - - - - - - - - - -
AA975773.01 BB00001 BB00001
AA975773.02 BB00002 BB00001
AA975773.03 BB00003 BB00001
AA975773.04 BB00004 BB00001
AA975773.04 BB00005 BB00001
AA231431.01 BB00011 BB00011
AA231431.02 BB00012 BB00012
AA231431.03 BB00013 BB00013
AA231431.04 BB00014 BB00014
…… ……… ………
我只想到前一步,即:select case when ordercode like '%.01' then shiptocode
else....
下一步卡住了希望各位前辈给个思路~多谢!
在一张表中有订单号和运单号两个栏位,格式如下:
ordercode shiptocode
- - - - - - - - - - - - - - - - - -
AA975773.01 BB00001
AA975773.02 BB00002
AA975773.03 BB00003
AA975773.04 BB00004
AA975773.04 BB00005
AA231431.01 BB00011
AA231431.02 BB00012
AA231431.03 BB00013
AA231431.04 BB00014
…… ………
现希望选出这样的一个新栏位,如果ordercode是主订单,即以.01结尾,则新栏位等于shiptocode,如果是副订单,即以其他数字结尾,则新栏位等于主订单的shiptocode。即选出如下格式:
ordercode shiptocode new
- - - - - - - - - - - - - - - - - - - - - - -
AA975773.01 BB00001 BB00001
AA975773.02 BB00002 BB00001
AA975773.03 BB00003 BB00001
AA975773.04 BB00004 BB00001
AA975773.04 BB00005 BB00001
AA231431.01 BB00011 BB00011
AA231431.02 BB00012 BB00012
AA231431.03 BB00013 BB00013
AA231431.04 BB00014 BB00014
…… ……… ………
我只想到前一步,即:select case when ordercode like '%.01' then shiptocode
else....
下一步卡住了希望各位前辈给个思路~多谢!
解决方案 »
- oralce 11g 32位 for linux RH5 安装神奇问题
- select To_char(1,'99') from dual 求解
- Oracle 动态列交叉表的问题
- 帮忙写个存储过程
- 一个SQL优化问题,在线等。。。。。
- JOB中存储过程有dbms_output.put_line,对该过程的UPDATE,INSERT,DELETE会有影响吗?
- 初学Oracle,问下Oracle的结构
- 昏倒~~~忘了用户名和密码
- unix下建库总是报错,请帮忙!
- 怎样用DBMS_JOB.SUBMIT()设置在每月的某天调用一个存储过程(急)。
- 怎么在gridview里删除某一行,还有基于oracle数据库的删除代码是什么?
- PLSQL如何实现等待1秒?
ordercode shiptocode new
- - - - - - - - - - - - - - - - - - - - - - -
AA975773.01 BB00001 BB00001
AA975773.02 BB00002 BB00001
AA975773.03 BB00003 BB00001
AA975773.04 BB00004 BB00001
AA975773.04 BB00005 BB00001
AA231431.01 BB00011 BB00011
AA231431.02 BB00012 BB00011
AA231431.03 BB00013 BB00011
AA231431.04 BB00014 BB00011
(
select 'AA975773.01' ordercode,'BB00001' shiptocode from dual union all
select 'AA975773.02','BB00002' from dual union all
select 'AA975773.03','BB00003' from dual union all
select 'AA975773.04','BB00004' from dual union all
select 'AA975773.04','BB00005' from dual union all
select 'AA231431.01','BB00011' from dual union all
select 'AA231431.02','BB00012' from dual union all
select 'AA231431.03','BB00013' from dual union all
select 'AA231431.04','BB00014' from dual
)
select ordercode
,shiptocode
,min(shiptocode) over(partition by substr(ordercode,1,instr(ordercode,'.')-1))
from a
;
ORDERCODE SHIPTOC MIN(SHI
----------- ------- -------
AA231431.03 BB00013 BB00011
AA231431.02 BB00012 BB00011
AA231431.01 BB00011 BB00011
AA231431.04 BB00014 BB00011
AA975773.04 BB00004 BB00001
AA975773.03 BB00003 BB00001
AA975773.02 BB00002 BB00001
AA975773.01 BB00001 BB00001
AA975773.04 BB00005 BB00001
with a as
(
select 'AA975773.01' ordercode,'BB00001' shiptocode from dual union all
select 'AA975773.02','BB00002' from dual union all
select 'AA975773.03','BB00003' from dual union all
select 'AA975773.04','BB00004' from dual union all
select 'AA975773.04','BB00005' from dual union all
select 'AA231431.01','BB00011' from dual union all
select 'AA231431.02','BB00012' from dual union all
select 'AA231431.03','BB00013' from dual union all
select 'AA231431.04','BB00014' from dual
)
select a.ordercode
,a.shiptocode
,b.shiptocode
from a,
(
select ordercode
,shiptocode
,row_number() over(partition by substr(ordercode,1,instr(ordercode,'.')-1) order by ordercode) rn
from a
)b
where substr(a.ordercode,1,instr(a.ordercode,'.')-1)=substr(b.ordercode,1,instr(b.ordercode,'.')-1)
and b.rn=1
;
ORDERCODE SHIPTOC SHIPTOC
----------- ------- -------
AA231431.04 BB00014 BB00011
AA231431.03 BB00013 BB00011
AA231431.02 BB00012 BB00011
AA231431.01 BB00011 BB00011
AA975773.04 BB00005 BB00001
AA975773.04 BB00004 BB00001
AA975773.03 BB00003 BB00001
AA975773.02 BB00002 BB00001
AA975773.01 BB00001 BB00001