17,089
社区成员
发帖
与我相关
我的任务
分享
SQL> drop table orderInfo;
Table dropped
SQL>
SQL> create table orderInfo
2 (
3 order_auto varchar2(22),
4 cust_no varchar2(22)
5 )
6 /
Table created
SQL>
SQL> create or replace trigger genOrderAuto
2 before insert on orderInfo
3 for each row
4 begin
5 select concat(substr(:new.cust_no,0,2),to_char(trunc(sysdate,'dd'),'yyyyMMdd')) into :new.order_auto from dual;
6 end;
7 /
Trigger created
SQL> insert into orderInfo values('sdfa','SLM23');
1 row inserted
SQL> select * from orderinfo;
ORDER_AUTO CUST_NO
---------------------- ----------------------
SL20100304 SLM23
SQL> commit;
Commit complete
SQL>
With a As
(Select 'SL2009110001' oa ,'SLM23' cn From dual Union All
Select 'SL2009110002' oa , 'SLM21' From dual
Union All
Select 'SL2009100001' oa , 'SLQ01' From dual
Union All
Select 'SL2009100003' oa , 'SLW02' From dual
Union All
Select 'DE2010030003' oa , 'DEW02' From dual)
Select Distinct cn1, t.cn1 || to_char(Sysdate,'YYYYMM')||lpad(to_number(t.sn1+1),4,0)
From (
Select oa1,cn1,sy1,
(case when oa1 = cn1||sy1 then Max(sn1) Else '0000' End) sn1
From (
Select
substr(oa,1,8) Oa1,
substr(cn,1,2) cn1,
substr(oa,9,4) sn1 ,
to_char(Sysdate,'YYYYMM') sy1
From a)
Group By oa1,cn1,sy1
) t