是检索条件, 我写一下我的烂sql,帮忙改进一下好吗? select c.offi_name,c.lce,c.tn,count(c.date_time),avg(c.tran_msu),avg(c.tran_msu),avg(c.rec_msu),avg(c.rec_msu),avg(c.tran_siosif),avg(c.tran_siosif),avg(c.rec_siosif),avg(c.rec_siosif),avg(c.tran_load),avg(c.rec_load) from (select a.offi_name,a.date_time,a.lce,a.tn,a.tran_siosif,a.tran_msu,a.rec_siosif,a.rec_msu,b.tran_load,b.rec_load from ( select * from lk_load_1h where offi_name in ('FZSTP1') and (To_Char(DATE_TIME,'YYYY-MM-DD')>='2002-09-15') And (To_Char(DATE_TIME,'YYYY-MM-DD')<='2002-09-18') And To_Char(DATE_TIME,'HH24') in ('13') ) a, ( select * from lk_load_1h_calc where offi_name in ('FZSTP1') and (To_Char(DATE_TIME,'YYYY-MM-DD')>='2002-09-15') And (To_Char(DATE_TIME,'YYYY-MM-DD')<='2002-09-18') And To_Char(DATE_TIME,'HH24') in ('13') ) b where a.offi_name=b.offi_name and a.lce=b.lce and a.tn=b.tn and a.date_time=b.date_time) c,link_info d where c.offi_name=d.a_offi_name and c.lce=d.chi_lce and c.tn=d.chi_tn group by c.offi_name,c.lce,c.tn
time的条件是离散的,比如8时/10时/13时。
以上两句是什么意思?检索条件吗?
如果数据量很大直接关联可能运行的速度比较慢,但如果有时间date_time 的限制,则限制后的数据量只要不是太大就可以直接关联。而且写临时表时也需要关联计算,速度应该和直接关联是一样的,但却多出了写临时表的工作。所以我认为直接关联好些。
另外,我认为如果你对这两个表的操作比较频繁,就不应该让数据量太大,应该增加一个历史表,定期将没用的数据归档,这样保证当前工作表的数据量不会太大。
select c.offi_name,c.lce,c.tn,count(c.date_time),avg(c.tran_msu),avg(c.tran_msu),avg(c.rec_msu),avg(c.rec_msu),avg(c.tran_siosif),avg(c.tran_siosif),avg(c.rec_siosif),avg(c.rec_siosif),avg(c.tran_load),avg(c.rec_load)
from
(select a.offi_name,a.date_time,a.lce,a.tn,a.tran_siosif,a.tran_msu,a.rec_siosif,a.rec_msu,b.tran_load,b.rec_load
from
(
select * from lk_load_1h where offi_name in ('FZSTP1')
and (To_Char(DATE_TIME,'YYYY-MM-DD')>='2002-09-15') And
(To_Char(DATE_TIME,'YYYY-MM-DD')<='2002-09-18') And To_Char(DATE_TIME,'HH24') in ('13')
) a,
(
select * from lk_load_1h_calc where offi_name in ('FZSTP1')
and (To_Char(DATE_TIME,'YYYY-MM-DD')>='2002-09-15') And
(To_Char(DATE_TIME,'YYYY-MM-DD')<='2002-09-18') And To_Char(DATE_TIME,'HH24') in ('13')
) b
where a.offi_name=b.offi_name
and a.lce=b.lce
and a.tn=b.tn
and a.date_time=b.date_time) c,link_info d
where c.offi_name=d.a_offi_name
and c.lce=d.chi_lce
and c.tn=d.chi_tn
group by c.offi_name,c.lce,c.tn