表A
a列 b列 c列
A 1 200803
A 4 200904
A 5 200910
B 1 200802
表B
a列 b列 c列 d列
A 2 200804 aaa
A 3 200806
A 4 200904 ccc
C 1 200801 ddd表A和表B的主键都是A列和C列(B列,没有实际意义,在这里加上b列只是表示顺序),以上两个表合并后,生成下面的表
(a列相同,c列break时,d列没有值的话,就设定前一条的值)
表C
a列 b列 c列 d列
A 1 200803 空白 // d列没有值,而且是第一条,所以为空白
A 2 200804 aaa // d列有值
A 3 200806 aaa // d列没有值,使用上一条的值
A 4 200904 ccc // ....
A 5 200910 ccc // ....
B 1 200802 空白 // 主键break了
C 1 200801 ddd // ....客户不让用游标,有其它方法吗?
a列 b列 c列
A 1 200803
A 4 200904
A 5 200910
B 1 200802
表B
a列 b列 c列 d列
A 2 200804 aaa
A 3 200806
A 4 200904 ccc
C 1 200801 ddd表A和表B的主键都是A列和C列(B列,没有实际意义,在这里加上b列只是表示顺序),以上两个表合并后,生成下面的表
(a列相同,c列break时,d列没有值的话,就设定前一条的值)
表C
a列 b列 c列 d列
A 1 200803 空白 // d列没有值,而且是第一条,所以为空白
A 2 200804 aaa // d列有值
A 3 200806 aaa // d列没有值,使用上一条的值
A 4 200904 ccc // ....
A 5 200910 ccc // ....
B 1 200802 空白 // 主键break了
C 1 200801 ddd // ....客户不让用游标,有其它方法吗?
select a,b,c,nvl(d,e)
from (select a,b,c,d,lag(d,1,'') over(partition by a order by b) e
from (select a.a a,a.b b,a.c c,null d from a
union
select b.a,b.b,b.c,b.d from b))
order by a,b,c;
a列 b列 c列 d列
A 1 200803 NULL
A 2 200804 aaa
A 3 200806 aaa
A 4 200904 ccc
A 5 200910 ccc
B 1 200802 NULL
C 1 200801 ddd 这样是对的。但是,
表A 再多一条数据,就不对了: a列 b列 c列
A 6 200911 表C
a列 b列 c列 d列
A 1 200803 NULL
A 2 200804 aaa
A 3 200806 aaa
A 4 200904 ccc
A 5 200910 ccc
A 6 200911 NULL //这列的值应该是ccc
B 1 200802 NULL
C 1 200801 ddd
select a,b,c,nvl(nvl(d,e),lag(e,1,'') over(partition by a order by b))
from (select a,b,c,d,lag(d,1,'') over(partition by a order by b) e
from (select a.a a,a.b b,a.c c,null d from a
union
select b.a,b.b,b.c,b.d from b))
order by a,b,c;
as
v_tmp varchar2(10);
i number:=1;
begin
select nvl(nvl(d,e),lag(e,i,'') over(partition by a order by b)) into v_tmp
from (select a,b,c,d,lag(d,1,'') over(partition by a order by b) e
from (select a.a a,a.b b,a.c c,null d from a
union
select b.a,b.b,b.c,b.d from b))
order by a,b,c;
if v_tmp is null then
loop
i:=i+1;
select nvl(nvl(d,e),lag(e,i,'') over(partition by a order by b)) into v_tmp
from (select a,b,c,d,lag(d,1,'') over(partition by a order by b) e
from (select a.a a,a.b b,a.c c,null d from a
union
select b.a,b.b,b.c,b.d from b))
order by a,b,c;
exit when v_tmp is not null;
end loop;
end if;
execute immediate 'select a,b,c,v_tmp
from (select a,b,c,d,lag(d,1,'''') over(partition by a order by b) e
from (select a.a a,a.b b,a.c c,null d from a
union
select b.a,b.b,b.c,b.d from b))
order by a,b,c';
end;
/
select a,b,c,max(d) over(partition by a order by b) e
from (select a.a a,a.b b,a.c c,null d from a
union
select b.a,b.b,b.c,b.d from b)
order by a,b,c