请教下:
如:
create table ttt
(
o number,
n varchar2(22)
);
create table sub_ttt
(
nn varchar2(33),
fo number
);insert into ttt values(1,'aaa');
insert into sub_ttt values('sub1',1);
insert into sub_ttt values('sub2',1);
insert into sub_ttt values('sub3',1);SQL> select t1.o,t1.n ,t2.nn
2 from
3 ttt t1,sub_ttt t2 where t1.o=t2.fo order by 3; O N NN
---------- ---------------------- ---------------------------------
1 aaa sub1
1 aaa sub2
1 aaa sub3
我现在想这样显示,请教下怎么写: O N NN
---------- ---------------------- ---------------------------------
1 aaa sub1
sub2
sub3
如:
create table ttt
(
o number,
n varchar2(22)
);
create table sub_ttt
(
nn varchar2(33),
fo number
);insert into ttt values(1,'aaa');
insert into sub_ttt values('sub1',1);
insert into sub_ttt values('sub2',1);
insert into sub_ttt values('sub3',1);SQL> select t1.o,t1.n ,t2.nn
2 from
3 ttt t1,sub_ttt t2 where t1.o=t2.fo order by 3; O N NN
---------- ---------------------- ---------------------------------
1 aaa sub1
1 aaa sub2
1 aaa sub3
我现在想这样显示,请教下怎么写: O N NN
---------- ---------------------- ---------------------------------
1 aaa sub1
sub2
sub3
select decode(lag(t1.o) over(partition by o, n order by nn),
t1.o,
null,
t1.o) o,
decode(lag(t1.n) over(partition by o, n order by nn),
t1.n,
null,
t1.n) n,
t2.nn
from ttt t1, sub_ttt t2
where t1.o = t2.fo
order by t1.o, t1.n, t2.nn;
要不就用not exists判断。