chengben/Scripts/资源系统局站表更新.sql

--资源系统局站表更新

--truncate table sqmdb_cost.station3;
--insert into sqmdb_cost.station3 
select 
s.station_sid,
s.station_sname,
d1.dicttext as city_name,
d2.dicttext as area_name,
d4.dicttext as property_type,
d5.dicttext as property_unit,
s.baidu_longitude as longitude,
s.baidu_latitude as latitude,
d6.dicttext as res_level,
d3.dicttext as station_type,
d7.dicttext as station_level,
s.tower_code,
s.grade_name,
s.wx_type,
s.oss_station
from sqmdb_cost.station s 
left join sqmdb_cost.dict d1 on d1.dictid=s.city_code::varchar
left join sqmdb_cost.dict d2 on d2.dictid=s.area_code::varchar
left join sqmdb_cost.dict d3 on d3.dictid=s.station_type::varchar
left join sqmdb_cost.dict d4 on d4.dictid=s.property_type::varchar
left join sqmdb_cost.dict d5 on d5.dictid=s.property_unit::varchar
left join sqmdb_cost.dict d6 on d6.dictid=s.stationlvl::varchar
left join sqmdb_cost.dict d7 on d7.dictid=s.station_level::varchar


--更新能耗索引表
insert into sqmdb_cost.station_nenghao 
select station_sid,unnest(string_to_array(oss_station,','))::numeric as oss_station from sqmdb_cost.station