forest_info.sql
· 983 B · MySQL
Orginalformat
create table test.zzz_forest_info_51 as
select
a.pnu,
b.sid_nm,
c.sgg_nm,
d.emd_nm,
e.ri_nm,
substring(a.pnu,12, 4)::integer::varchar as bon,
substring(a.pnu,16, 4)::integer::varchar as bu,
trim(substring(a.jibun,0, length(a.jibun))) as jibun,
right(a.jibun, 1) as jimok,
a.area_hectare,
a.jukjung,
a.jukjung1,
a.jaebe,
a.jaebe1,
a.na_imsang,
a.na_yunggub,
a.na_kyunggub,
a.na_mildo,
a.to_jiri,
a.to_jihyung,
a.to_moam,
a.to_tosim,
a.to_yugi,
a.to_tosung,
a.to_geunsup,
a.to_geunmildo,
a.avg_height,
a.avg_slope,
a.avg_aspect,
a.label
from public.zzz_forest_info_51 a
left outer join comtn_map_admindistrict_sido b
on left(a.pnu,2) = b.sid_cd
left outer join comtn_map_admindistrict_sgg c
on left(a.pnu,5) = c.sgg_cd
left outer join comtn_map_admindistrict_emd d
on left(a.pnu,8) = d.emd_cd
left outer join comtn_map_admindistrict_ri e
on left(a.pnu,10) = e.ri_cd
| 1 | create table test.zzz_forest_info_51 as |
| 2 | select |
| 3 | a.pnu, |
| 4 | b.sid_nm, |
| 5 | c.sgg_nm, |
| 6 | d.emd_nm, |
| 7 | e.ri_nm, |
| 8 | substring(a.pnu,12, 4)::integer::varchar as bon, |
| 9 | substring(a.pnu,16, 4)::integer::varchar as bu, |
| 10 | trim(substring(a.jibun,0, length(a.jibun))) as jibun, |
| 11 | right(a.jibun, 1) as jimok, |
| 12 | a.area_hectare, |
| 13 | a.jukjung, |
| 14 | a.jukjung1, |
| 15 | a.jaebe, |
| 16 | a.jaebe1, |
| 17 | a.na_imsang, |
| 18 | a.na_yunggub, |
| 19 | a.na_kyunggub, |
| 20 | a.na_mildo, |
| 21 | a.to_jiri, |
| 22 | a.to_jihyung, |
| 23 | a.to_moam, |
| 24 | a.to_tosim, |
| 25 | a.to_yugi, |
| 26 | a.to_tosung, |
| 27 | a.to_geunsup, |
| 28 | a.to_geunmildo, |
| 29 | a.avg_height, |
| 30 | a.avg_slope, |
| 31 | a.avg_aspect, |
| 32 | a.label |
| 33 | from public.zzz_forest_info_51 a |
| 34 | left outer join comtn_map_admindistrict_sido b |
| 35 | on left(a.pnu,2) = b.sid_cd |
| 36 | left outer join comtn_map_admindistrict_sgg c |
| 37 | on left(a.pnu,5) = c.sgg_cd |
| 38 | left outer join comtn_map_admindistrict_emd d |
| 39 | on left(a.pnu,8) = d.emd_cd |
| 40 | left outer join comtn_map_admindistrict_ri e |
| 41 | on left(a.pnu,10) = e.ri_cd |