比如说,我有表a表b,a的unid跟b的a_unid对应但不是一一对应关系a.unid=100b里有两列与a对应
列1:b.a_unid=100,b.nr=b1,b.name=tom
列2:b.a_unid=100,b.nr=b2,b.name=jerry现在我希望用select查询语句查询后,显示以下一列两行内容:100 b1/tom,b2/jerry有办法吗?
列1:b.a_unid=100,b.nr=b1,b.name=tom
列2:b.a_unid=100,b.nr=b2,b.name=jerry现在我希望用select查询语句查询后,显示以下一列两行内容:100 b1/tom,b2/jerry有办法吗?
FROM a, b
WHERE b.a_unid = a.unid
GROUP BY a.unid
select t.id,wmsys.wm_concat(t.v )
from (select a.id,b.nr||'/'||b.name v from p1 a ,p2 b where a.id = b.id) t
group by t.id;
select id,
max(ltrim(sys_connect_by_path(v, ','),',')) role
-- max(ltrim(sys_connect_by_path(v, ','), ','))role
from
(
select t.id,
t.v,
row_number() over(partition by t.id order by v) rn
from (select a.id,b.nr||'/'||b.name v from p1 a ,p2 b where a.id = b.id) t
)
start with rn = 1
connect by prior rn = rn-1
and prior id = id
group by id