oracle 多表查询

oracle 多表查询  1对多    多个数据放到一个字段内

select  id,zhzxm,fzhzxm,ltrim(max(sys_connect_by_path(xm, ',')), ',') xm
from (
select id,zhzxm,fzhzxm,xm,row_number() over(partition by id order by lrsj) rn  --标记相同的字段
from
(select t2.id id,t2.zhzxm zhzxm,t2.fzhzxm fzhzxm ,t1.xm xm,t1.lrsj lrsj from t_yj_yjzhy t1 left join t_yj_yjzhzxx t2 on t1.yjzhzid = t2.id)
)
start with rn=1
connect by prior rn=rn-1 and prior id=id
group by id,zhzxm,fzhzxm