两张简单的表,请赐教一条语句
表1
编号 名称 时间
001 abc 2006
002 bcd 2007
003 cde 2008
表2
附件编号 编号 附件名称
0001 001 牙膏
0002 001 袜子
0003 002 香皂
求一条语句能能产生以下结果
沧桑老男人(81046720) 23:09:57
编号 名称 时间 附件
001 abc 2006 牙膏，袜子
不能用存储过程，就一条语句完成
[解决办法]
这个应该可以：
create table test1(no varchar2(10),name varchar2(10),time varchar2(10))
create table test2(no2 varchar2(10),no varchar2(10),name2 varchar2(10))
insert into test1 values( '001 ', 'abc ', '2006 ');
insert into test1 values( '002 ', 'bcd ', '2007 ');
insert into test1 values( '003 ', 'cde ', '2008 ');
insert into test2 values( '0001 ', '001 ', '牙膏 ');
insert into test2 values( '0002 ', '001 ', '袜子 ');
insert into test2 values( '0003 ', '002 ', '香皂 ');
insert into test2 values( '0004 ', '003 ', '毛巾 ');
commit;
select t3.no,t3.name,t3.time,
(select ltrim(max(sys_connect_by_path(b.name2, '、 ')), '、 ') value
from
(select a.no,a.name,a.time,a.name2,a.rn,lead(a.rn) over(partition by a.no order by a.rn) rn1
from
(select t1.no,t1.name,t1.time,t2.name2,row_number() over(order by t1.no) rn
from test1 t1,test2 t2
where t1.no=t2.no) a) b
start with b.no=t3.no
and b.rn1 is null
connect by b.rn1=prior b.rn) v
from
(select * from test1) t3
[解决办法]
zealot_001() 的方法可以.
[解决办法]
select id,name1,timeDate
,ltrim(max(sys_connect_by_path(name2, ', ')), ', ') name2
from (
select a.id,a.name as name1,a.timeDate,b.id_p,b.name as name2
,row_number()over(order by a.id)
+dense_rank()over(order by a.id) as rn
from mtb1 a,mtb2 b
where a.id=b.id_p
)
start with rn=2
connect by prior rn=rn-1
group by id,name1,timeDate
[解决办法]
decode()
