oracel中将子表中一个字段多条数据合并到主表的sql

select f.* ，g.nsjgName, h.XCJZZZZ, h.XCJFZZZ
  from (select max(t.JGSY_NAMEs) JGSY_NAMEs,
               max(t.jgsy_gg) jgsy_gg,
               jgsy_code,
               sum(t.zcjzs) zcjzs,
               sum(t.fcjzs) fcjzs,
               listagg(t.pzwh, ',') within group(order by t.jgsy_code) pzwh,
               listagg(t.BEIZHU, ',') within group(order by t.jgsy_code) beizhu
          from s_zsys t
         where t.BLRESULT = '3'
           and substr(t.jgsy_code, 0, 6) = '320100'
         group by jgsy_code
         order by t.jgsy_code) f
  left join (select jgsy_parent_code,
                    listagg(jgsy_name, ',') within group(order by gen.jgsy_parent_code) nsjgName
               from jgsy_common gen
              where 1 = 1
                and gen.jgsy_type = '06'
              group by jgsy_parent_code) g
    on g.jgsy_parent_code = f.jgsy_code
  left join BMLDZS h
    on h.jgsy_code = f.jgsy_code;



--select * from jgsy_common  f where f.jgsy_parent_code='320100-01-10012'  and f.jgsy_type='06'

注意用这个函数可以知道那个是统计列【grouping函数】
select max(code) code,case when grouping (code) = 1 then '0'  end  orders,max(name) name,

　　

grouping