表1:
时间,单号(单号为递增,但是不一定是连续)
20080927,S201012437
20080927,S201012437
20080927,S201012438
20080927,S201012440
....要求得到:流水号,时间,单号
20080927000001 ,20080927,S201012437
20080927000001 ,20080927,S201012437
20080927000002 ,20080927,S201012438
20080927000003 ,20080927,S201012440
......
时间,单号(单号为递增,但是不一定是连续)
20080927,S201012437
20080927,S201012437
20080927,S201012438
20080927,S201012440
....要求得到:流水号,时间,单号
20080927000001 ,20080927,S201012437
20080927000001 ,20080927,S201012437
20080927000002 ,20080927,S201012438
20080927000003 ,20080927,S201012440
......
然后用日期+SEQ
SELECT lpad(时间 || DR,6,'0') 流水号, 时间, 单号
FROM (SELECT DENSE_RANK() OVER(ORDER BY 单号) DR, 时间, 单号
FROM YOURTABLE);
create table t(pk number primary key,date1,id1);
create sequence t_seq start 20080927000001;
create sequence t1_seq start 201012437;
create trigger t_trigger before insert on t for each row
begin
insert into t values(t_seq.nextval,sysdate,'S'||t1_seq.nextval);
end;
create table t(pk number primary key,date1 date,id1 varchar2(10));
create sequence t_seq start 20080927000001;
create sequence t1_seq start 201012437;
create trigger t_trigger before insert on t for each row
begin
insert into t values(t_seq.nextval,sysdate,'S'||to_char(t1_seq.nextval));
end;
create sequence seq1;
2、再创建触发器如下:
create or replace trigger tr before insert on a for each row
begin
select to_char(sysdate, 'yyyymmdd') || lpad(seq1.nextval, 6, 0)
into :new.id
from dual;
end;
from tablename
order by 单号
20080927000001 ,20080927,S201012437
20080927000001 ,20080927,S201012437
====================================
这样是不行的,数据量大了以后,如果不及早建立主关键字的话,会影响查询等操作的速度。