数据库中生成数据透视表的SQL的效率比较

王朝mssql·作者佚名  2006-04-24
窄屏简体版  字體: |||超大  

(1)以下SQL语句执行效率较低,大约在90分钟执行完毕

select distinct a.pulse_id,a.field_id,

(select sum(kwh_val) from his_pbs_20041201 where pulse_id = a.pulse_id and to_number(to_char(unix_to_oracle(occur_time),'hh24'))=0 ) as v0,

(select sum(raw_val) from his_pbs_20041201 where pulse_id = a.pulse_id and to_number(to_char(unix_to_oracle(occur_time),'hh24'))=0 ) as r0,

(select sum(status) from his_pbs_20041201 where pulse_id = a.pulse_id and to_number(to_char(unix_to_oracle(occur_time),'hh24'))=0 ) as st0,

from his_pbs_20041201 a

where pulse_id = 164000029;

(2)通过在SQL中加入Case语句,可以极大提高SQL的执行速度

select distinct a.pulse_id,a.field_id,

sum(case to_number(to_char(unix_to_oracle(occur_time),'hh24')) when 0 then kwh_val else 0 end) as v0,

sum(case to_number(to_char(unix_to_oracle(occur_time),'hh24')) when 0 then raw_val else 0 end) as r0,

sum(case to_number(to_char(unix_to_oracle(occur_time),'hh24')) when 0 then status else 0 end) as st0,

from his_pbs_20041201 a

group by a.pulse_id,a.field_id;

以上两种取得数据透视表的方法在Oracle与Sql Server中均适用。第一种方法要耗时90分钟,第二种方法只需3分钟即可。

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有  導航