with t1 as
(select 1 sid, 'lily' sname
from dual
union all
select 2 sid, 'jimmy' sname from dual),
t2 as
(select 1 sid, 1 cid
from dual
union all
select 1 sid, 2 cid
from dual
union all
select 1 sid, 3 cid
from dual
union all
select 2 sid, 1 cid
from dual
union all
select 2 sid, 3 cid from dual)
select sid, sname, max(cids) cids
from (select t1.sid,
sname,
wm_concat(cid) over(partition by t1.sid, sname order by cid) cids
from t1, t2
where t1.sid = t2.sid)
group by sid, sname
(select 1 sid, 'lily' sname
from dual
union all
select 2 sid, 'jimmy' sname from dual),
t2 as
(select 1 sid, 1 cid
from dual
union all
select 1 sid, 2 cid
from dual
union all
select 1 sid, 3 cid
from dual
union all
select 2 sid, 1 cid
from dual
union all
select 2 sid, 3 cid from dual)
select sid, sname, max(cids) cids
from (select t1.sid,
sname,
wm_concat(cid) over(partition by t1.sid, sname order by cid) cids
from t1, t2
where t1.sid = t2.sid)
group by sid, sname
(select 1 sid, 'lily' sname
from dual
union all
select 2 sid, 'jimmy' sname from dual),
t2 as
(select 1 sid, 1 cid
from dual
union all
select 1 sid, 2 cid
from dual
union all
select 1 sid, 3 cid
from dual
union all
select 2 sid, 1 cid
from dual
union all
select 2 sid, 3 cid from dual)
select sid, sname, max(cids) cids
from (select t1.sid,
sname,
wm_concat(cid) over(partition by t1.sid, sname order by cid) cids
from t1, t2
where t1.sid = t2.sid)
group by sid, sname
oracle wm_concat(column)函数使我们经常会使用到的,下面就教您如何使用oracle wm_concat(column)函数实现字段合并,如果您对oracle wm_concat(column)函数使用方面感兴趣的话,不妨一看。shopping:-----------------------------------------u_id goods num------------------------------------------1 苹果 22 梨子 51 西瓜 43 葡萄 13 香蕉 11 橘子 3=======================想要的结果为:--------------------------------u_id goods_sum____________________1 苹果,西瓜,橘子2 梨子3 葡萄,香蕉---------------------------------select u_id, wmsys.wm_concat(goods) goods_sum
from shopping
group by u_id
想要的结果2:--------------------------------u_id goods_sum____________________1 苹果(2斤),西瓜(4斤),橘子(3斤)2 梨子(5斤)3 葡萄(1斤),香蕉(1斤)---------------------------------使用oracle wm_concat(column)函数实现:select u_id, wmsys.wm_concat(goods || '(' || num || '斤)' ) goods_sum
from shopping
group by u_id
select s.sid, s.sname, wm_concat(sc.cid) as cids
from s
left join sc
on s.sid = sc.sid
group by s.sid, s.sname;
from ( select sid ,name, null from S) union all ( select sid ,null cid from S)
group by sid;