表连接不显示相同值得列

[db2_aixer]

请教下：
如：
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

[hh7yx]

应该还有更好的方法，刚睡醒，只能想个土办法：

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;

• 打赏
• 举报

回复 赞

[db2_aixer]

thanks

• 打赏
• 举报

回复 赞

[shy315]

引用 1 楼 u010412956 的回复:

应该还有更好的方法，刚睡醒，只能想个土办法：

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;

或者用row_number over=1判断， 要不就用not exists判断。

• 打赏
• 举报

回复 赞

[白脑哥]

学习了

• 打赏
• 举报

回复 赞