create table t(a varchar2(10));

insert into t values('b');

insert into t values(null);

insert into t values(null);

insert into t values('a');

insert into t values('e');

insert into t values(null);

insert into t values('c');

insert into t values('d');

要求按自然顺序查出所有记录，并为每行非空记录加一个行号。
形如

子查询 O(n):

select t3.a,decode(t3.a,null,null,num) num from(

    select t2.a,sum(t2.c) over(order by t2.r) num from (

        select rownum r,decode(t.a,null,null,1) c, a from t

    )t2

)t3;

外连接O(2n):

select t2.a,t2.r from

(select a,rownum r from t) t1

left join

(select a,rownum r from t where a is not null) t2

on t1.a=t2.a order by t1.r;

王工的版本

select t.a,nvl2(a,count(a) over(order by rownum),'') num from t;

ITPub博客

给非空记录增加行号

最新文章