放到临时表里with t as ( select d.clusterno, SMSDRIW, smsdrie, smsdris, smsdrin from rect500_hangzhour r, TBCLUSTERTOGRID d where r.mi_name = d.rectsmid ) select clusterno, clustername, description, (select count(*) from TBCLUSTERTOGRID t where t.clusterno = a.clusterno) as gridCount, (select count(*) from cell_0571 c, where (c.ilong > t.SMSDRIW and c.ilong < t.smsdrie) and (c.ilat > t.smsdris and c.ilat < t.smsdrin) and t.clusterno = a.clusterno) as BaseStation, (select count(*) from site_0571 s, (select d.clusterno, SMSDRIW, smsdrie, smsdris, smsdrin from rect500_hangzhour r, TBCLUSTERTOGRID d where r.mi_name = d.rectsmid) t where (s.ilong > t.SMSDRIW and s.ilong < t.smsdrie) and (s.ilat > t.smsdris and s.ilat < t.smsdrin) and t.clusterno = a.clusterno) as CiCount, '0' as Clusterteltraffic from tbclusters a;
with t as ( select d.clusterno, SMSDRIW, smsdrie, smsdris, smsdrin from rect500_hangzhour r, TBCLUSTERTOGRID d where r.mi_name = d.rectsmid ) select clusterno, clustername, description, (select count(*) from TBCLUSTERTOGRID t where t.clusterno = a.clusterno) as gridCount, (select count(*) from cell_0571 c where (c.ilong > t.SMSDRIW and c.ilong < t.smsdrie) and (c.ilat > t.smsdris and c.ilat < t.smsdrin) and t.clusterno = a.clusterno) as BaseStation, (select count(*) from site_0571 s where (s.ilong > t.SMSDRIW and s.ilong < t.smsdrie) and (s.ilat > t.smsdris and s.ilat < t.smsdrin) and t.clusterno = a.clusterno) as CiCount, '0' as Clusterteltraffic from tbclusters a;
这个报错了 and t.clusterno = a.clusterno) as BaseStation, t.clusterno 这里报错
哦,忘记把 t from进去了,着急忘记了,不过还能优化点吗,我这个数据很多,效率很慢啊,差不多要两分钟了
(
select d.clusterno, SMSDRIW, smsdrie, smsdris, smsdrin
from rect500_hangzhour r, TBCLUSTERTOGRID d
where r.mi_name = d.rectsmid
)
select clusterno,
clustername,
description,
(select count(*)
from TBCLUSTERTOGRID t
where t.clusterno = a.clusterno) as gridCount,
(select count(*)
from cell_0571 c,
where (c.ilong > t.SMSDRIW and c.ilong < t.smsdrie)
and (c.ilat > t.smsdris and c.ilat < t.smsdrin)
and t.clusterno = a.clusterno) as BaseStation,
(select count(*)
from site_0571 s,
(select d.clusterno, SMSDRIW, smsdrie, smsdris, smsdrin
from rect500_hangzhour r, TBCLUSTERTOGRID d
where r.mi_name = d.rectsmid) t
where (s.ilong > t.SMSDRIW and s.ilong < t.smsdrie)
and (s.ilat > t.smsdris and s.ilat < t.smsdrin)
and t.clusterno = a.clusterno) as CiCount,
'0' as Clusterteltraffic
from tbclusters a;
with t as
(
select d.clusterno, SMSDRIW, smsdrie, smsdris, smsdrin
from rect500_hangzhour r, TBCLUSTERTOGRID d
where r.mi_name = d.rectsmid
)
select clusterno,
clustername,
description,
(select count(*)
from TBCLUSTERTOGRID t
where t.clusterno = a.clusterno) as gridCount,
(select count(*)
from cell_0571 c
where (c.ilong > t.SMSDRIW and c.ilong < t.smsdrie)
and (c.ilat > t.smsdris and c.ilat < t.smsdrin)
and t.clusterno = a.clusterno) as BaseStation,
(select count(*)
from site_0571 s
where (s.ilong > t.SMSDRIW and s.ilong < t.smsdrie)
and (s.ilat > t.smsdris and s.ilat < t.smsdrin)
and t.clusterno = a.clusterno) as CiCount,
'0' as Clusterteltraffic
from tbclusters a;
这个报错了
and t.clusterno = a.clusterno) as BaseStation,
t.clusterno 这里报错
哦,忘记把 t from进去了,着急忘记了,不过还能优化点吗,我这个数据很多,效率很慢啊,差不多要两分钟了