试试下面的sql:
SELECT lpad(时间 || DR,6,'0') 流水号, 时间, 单号
  FROM (SELECT DENSE_RANK() OVER(ORDER BY 单号) DR, 时间, 单号
          FROM YOURTABLE);

------解决方案--------------------
lpad+DENSE_RANK()

------解决方案--------------------
SQL code

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;

------解决方案--------------------
SQL code

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;

------解决方案--------------------
如果之前有数据现在要自动生成流水号列的话用2楼的答案，如果以后要自动生成序列号的话要用触发器：
SQL code

1、首先创建序列
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;

------解决方案--------------------
select 时间 || Lpad(rownum,6,0) as 流水号，时间，单号
from tablename
order by 单号