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 ’字段‘
