select a.xcbh '现场编号',a.xz '被征收人' 
 ,thz_fzmj '土地有证',round(thz_tdmj-thz_fzmj,2) '土地无证',thz_tdmj '土地合计（㎡)',i.房屋框架,i.房屋混合,i.房屋砖木,i.房屋面积合计㎡,t.主要附属物合计㎡_m³,g.其他附属物及青苗
 from x as a 
 left join typg_house_zd as z on z.thz_xcbh=a.xcbh
 left join typg_house as h on h.th_xcbh=a.xcbh
 left join (select thi_xcbh,
    round(sum(case when thi_fwjg='框架结构' then thi_buildmj  else 0  end),2) '房屋框架',
round(sum(case when thi_fwjg='混合结构' then thi_buildmj  else 0  end),2) '房屋混合',
round(sum(case when thi_fwjg='砖木结构' then thi_buildmj  else 0  end),2) '房屋砖木',
round(sum( thi_buildmj ),2) '房屋面积合计㎡'
    from typg_house_info where del_flag=0 group by thi_xcbh ) i on i.thi_xcbh=a.xcbh
  left join (select tht_xcbh,
round(sum( tht_num ),2) '主要附属物合计㎡_m³'
    from typg_house_tag where del_flag=0 and (tht_unit='m' or tht_unit='㎡'  or tht_unit='m²' or tht_unit='m³') group by tht_xcbh ) t on t.tht_xcbh=a.xcbh
left join (select tht_xcbh,GROUP_CONCAT(tht_fswmc,'',round(tht_num,0),'',tht_unit separator '|') as 其他附属物及青苗
from (select * FROM typg_house_tag where del_flag=0 and tht_unit<>'m' and tht_unit<>'㎡' and tht_unit<>'m²' and tht_unit<>'m³') f GROUP BY tht_xcbh ) g on g.tht_xcbh=a.xcbh
where a.xcbh is not null



 

GROUP_CONCAT(tht_fswmc,'',round(tht_num,0),'',tht_unit separator '|') as ’字段‘