select t.instanceid,
t1.related_ne6,
t1.related_ne5,
t1.school_name,
t.cover_type,
'LTE' as net_type,
t1.resname,
t1.lac,
t1.ci,
t1.cell_id,
'重庆' as area,
'' as elses
from temp_school_zhc t1
left join uia_date_eutrancell t
on t1.ci||t1.cell_id=t.enodeb_id||t.eutrancell_id
/* t.enodeb_id = t1.ci
and t.eutrancell_id = t1.cell_id*/
where t.uia_time_id = 20150526
and t1.instanceid is null;
--and t1.ci||t1.cell_id is null;
--and t1.ci||t1.cell_id=160712
temp_school_zhc 表中instanceid为空的有2405条,可是查询结果之后只有2155条。left join之后为什么查询出来的数据偏少,原因为 where子句将left join之后的结果过滤掉了一部分。更改为如下之后查询的结果变为2405条了。
select t.instanceid,
t1.related_ne6,
t1.related_ne5,
t1.school_name,
t.cover_type,
'LTE' as net_type,
t1.resname,
t1.lac,
t1.ci,
t1.cell_id,
'重庆' as area,
'' as elses
from (select * from temp_school_zhc t where t.instanceid is null) t1
left join (select * from uia_date_eutrancell t where t.uia_time_id = 20150526) t
on t1.ci||t1.cell_id=t.enodeb_id||t.eutrancell_id
/* t.enodeb_id = t1.ci
and t.eutrancell_id = t1.cell_id*/
-- where t.uia_time_id = 20150526
--and t1.ci||t1.cell_id is null;
--and t1.ci||t1.cell_id=332111
虽然按照oracle的方式,where语句从下往上执行,可是执行的结果还是按照人的思维从上往下执行。
t1.related_ne6,
t1.related_ne5,
t1.school_name,
t.cover_type,
'LTE' as net_type,
t1.resname,
t1.lac,
t1.ci,
t1.cell_id,
'重庆' as area,
'' as elses
from temp_school_zhc t1
left join uia_date_eutrancell t
on t1.ci||t1.cell_id=t.enodeb_id||t.eutrancell_id
/* t.enodeb_id = t1.ci
and t.eutrancell_id = t1.cell_id*/
where t.uia_time_id = 20150526
and t1.instanceid is null;
--and t1.ci||t1.cell_id is null;
--and t1.ci||t1.cell_id=160712
temp_school_zhc 表中instanceid为空的有2405条,可是查询结果之后只有2155条。left join之后为什么查询出来的数据偏少,原因为 where子句将left join之后的结果过滤掉了一部分。更改为如下之后查询的结果变为2405条了。
select t.instanceid,
t1.related_ne6,
t1.related_ne5,
t1.school_name,
t.cover_type,
'LTE' as net_type,
t1.resname,
t1.lac,
t1.ci,
t1.cell_id,
'重庆' as area,
'' as elses
from (select * from temp_school_zhc t where t.instanceid is null) t1
left join (select * from uia_date_eutrancell t where t.uia_time_id = 20150526) t
on t1.ci||t1.cell_id=t.enodeb_id||t.eutrancell_id
/* t.enodeb_id = t1.ci
and t.eutrancell_id = t1.cell_id*/
-- where t.uia_time_id = 20150526
--and t1.ci||t1.cell_id is null;
--and t1.ci||t1.cell_id=332111
虽然按照oracle的方式,where语句从下往上执行,可是执行的结果还是按照人的思维从上往下执行。