table1
id1 name1
a asdf
b qwertable2
id2 name2 id1
1 ssss a
2 sss a
3 sssss b
4 ss b
怎么得到如下结果,即把table2中所有对应id1的信息连成一个字符串
id1 name1 desc1
a asdf 1|ssss;2|sss
b qwer 3|sssss;4|ss
id1 name1
a asdf
b qwertable2
id2 name2 id1
1 ssss a
2 sss a
3 sssss b
4 ss b
怎么得到如下结果,即把table2中所有对应id1的信息连成一个字符串
id1 name1 desc1
a asdf 1|ssss;2|sss
b qwer 3|sssss;4|ss
解决方案 »
- merge partition问题
- pl/sql 中, exception处理,when then之间可不可以什么也不写?
- jdbc或者pl/sql怎么获取函数的参数
- SQL语句补0的问题
- 求SQL存储过程转ORACLE过程
- 存储过中的execute immediate问题
- 登录oracle时出现ORA-01033:ORACLE initialization or shutdown in progress错误
- 存储过程调用问题!
- 急求助???
- 关于 Single Sign-On 的问题?请教,谢谢!Waitting...
- 几个应用服务器都指向这个Oracle数据库服务器有没有问题呢?
- 请高手指教下求总数的问题
(
select 'a' id1, 'asdf' name1 from dual
union all
select 'b' id1, 'qwer' name1 from dual
),
tmp2 as
(
select 1 id2, 'ssss' name2, 'a' id1 from dual
union all
select 2 id2, 'sss' name2, 'a' id1 from dual
union all
select 3 id2, 'sssss' name2, 'b' id1 from dual
union all
select 4 id2, 'ss' name2, 'b' id1 from dual
)
select tmp2.id1,tmp1.name1,wm_concat(tmp2.id2 || '|' || tmp2.name2)
from tmp2, tmp1
where tmp2.id1 = tmp1.id1
group by tmp2.id1,tmp1.name1;
select id1,name1,desc1
from (select id1,
name1,
desc1,
row_number() over(partition by id1, name1 order by id2 desc) rn
from (select id1,
id2,
name1,
substr(sys_connect_by_path(id2 || '|' || name2, ';'),
2) desc1
from (select t.*,
row_number() over(partition by id1 order by id2) rn,
t.id1 || row_number() over(partition by id1 order by id2) rn1,
t.id1 ||
(row_number()
over(partition by id1 order by id2) - 1) rn2
from (select t1.id1, t1.name1, t2.id2, t2.name2
from test1 t1, test2 t2
where t1.id1 = t2.id1) t)
start with rn = 1
connect by rn2 = prior rn1))
where rn = 1id1 name1 desc1
-----------------------
a asdf 1|ssss;2|sss
b qwer 3|sssss;4|ss