drop table if exists view_house_price;
drop view if exists view_house_price;
create view view_house_price as
select a.id              as id,
       a.houses          as houses,
       '2导入'             as source,
       a.provinceName    as provinceName,
       a.cityName        as cityName,
       a.areaName        as areaName,
       a.bizName         as bizName,
       a.address         as address,
       a.price           as price,
       a.area            as area,
       a.money           as money,
       a.priceDate       as priceDate,
       a.longitude       as longitude,
       a.latitude        as latitude,
       a.createdDatetime as createdDatetime
from db_price_community_import a
union all
select b.id              as id,
       b.houses          as houses,
       '1链家'             as source,
       b.provinceName    as provinceName,
       b.cityName        as cityName,
       b.areaName        as areaName,
       b.bizName         as bizName,
       b.address         as address,
       b.dealPrice       as price,
       b.buildArea       as area,
       b.dealMoney       as money,
       b.dealDate        as priceDate,
       b.longitude       as longitude,
       b.latitude        as latitude,
       b.createdDatetime as createdDatetime
from db_data_lianjia b
where (b.floorInfo is null or b.floorInfo not like  '地下室%')
--    union all
--    select au.id              as id,
--           au.houses          as houses,
--           '3司法'              as source,
--           au.provinceName    as provinceName,
--           au.cityName        as cityName,
--           au.areaName        as areaName,
--           au.bizName         as bizName,
--           au.address         as address,
--           au.price           as price,
--           au.buildArea       as area,
--           au.dealPrice       as money,
--           au.dealDate        as priceDate,
--           au.longitude       as longitude,
--           au.latitude        as latitude,
--           au.createdDatetime as createdDatetime
--    from db_data_auction au
union all
select o.id              as id,
       o.houses          as houses,
       '4个贷'             as source,
       o.provinceName    as provinceName,
       o.cityName        as cityName,
       o.areaName        as areaName,
       o.bizName         as bizName,
       o.address         as address,
       o.price           as price,
       o.buildArea            as area,
       o.money           as money,
       o.priceDate       as priceDate,
       o.longitude       as longitude,
       o.latitude        as latitude,
       o.createdDatetime as createdDatetime
from db_data_order o
union all
select up.id              as id,
       up.houses          as houses,
       '5链家挂牌'         as source,
       up.provinceName    as provinceName,
       up.cityName        as cityName,
       up.areaName        as areaName,
       up.bizName         as bizName,
       up.address         as address,
       up.upPrice*10000/up.buildArea       as price,
       up.buildArea       as area,
       up.upPrice*10000   as money,
       up.up_date        as priceDate,
       up.longitude       as longitude,
       up.latitude        as latitude,
       up.createdDatetime         as createdDatetime
from db_data_lianjia_up up
    where up.houseUsage <> '车库'