语句如下:
select a.lsh,a.dwdm,a.dwmc,
a.sendedsurplus
-(select sum(b.ba_money) from a_fina_balance b where b.khlsh=a.lsh and to_char(b.js_date,'yyyy-MM-dd HH:mm:ss')between'2007-09-01 00:00:01'and'2007-09-30 00:00:01')
+(select sum(c.sfs*c.jsj) hk from a_yfd c where c.khlsh=a.lsh and to_char(c.yxqqssj,'yyyy-MM-dd')between'2007-09-01'and'2007-09-30')qcye,
(select sum(b.ba_money) from a_fina_balance b where b.khlsh=a.lsh and to_char(b.js_date,'yyyy-MM-dd HH:mm:ss')between'2007-09-01 00:00:01'and'2007-09-30 00:00:01')hlzj,
(select sum(c.sfs*c.jsj) hk from a_yfd c where c.khlsh=a.lsh and to_char(c.yxqqssj,'yyyy-MM-dd')between'2007-09-01'and'2007-09-30')yszk,
a.sendedsurplus
from a_wldw a where a.dwmc = '淮阴医疗器械有限公司';
在PLSQL中查询的时间为一条记录0.75秒
表中只有一条记录。
select a.lsh,a.dwdm,a.dwmc,
a.sendedsurplus
-(select sum(b.ba_money) from a_fina_balance b where b.khlsh=a.lsh and to_char(b.js_date,'yyyy-MM-dd HH:mm:ss')between'2007-09-01 00:00:01'and'2007-09-30 00:00:01')
+(select sum(c.sfs*c.jsj) hk from a_yfd c where c.khlsh=a.lsh and to_char(c.yxqqssj,'yyyy-MM-dd')between'2007-09-01'and'2007-09-30')qcye,
(select sum(b.ba_money) from a_fina_balance b where b.khlsh=a.lsh and to_char(b.js_date,'yyyy-MM-dd HH:mm:ss')between'2007-09-01 00:00:01'and'2007-09-30 00:00:01')hlzj,
(select sum(c.sfs*c.jsj) hk from a_yfd c where c.khlsh=a.lsh and to_char(c.yxqqssj,'yyyy-MM-dd')between'2007-09-01'and'2007-09-30')yszk,
a.sendedsurplus
from a_wldw a where a.dwmc = '淮阴医疗器械有限公司';
在PLSQL中查询的时间为一条记录0.75秒
表中只有一条记录。
可以修改成
b.js_date between to_date('2007-09-01 00:00:01','yyyy-mm-dd hh24:mi:ss') and to_date('2007-09-30 00:00:01','yyyy-mm-dd hh24:mi:ss')
因为日期搜索会比字符比较快。
而且统计数据太多,看过了后是否有比较大的提高。
where c.khlsh=a.lsh
and c.yxqqssj between to_date('2007-09-01','yyyy-mm-dd') and to_date('2007-09-30','yyyy-mm-dd')
and b.khlsh=a.lsh
and b.js_date between to_date('2007-09-01','yyyy-mm-dd') and to_date('2007-09-30','yyyy-mm-dd')
group by a.lsh------------
把上面的语句 和 你那个a_wldw 关联起来不就ok 了 ,
整那么多select 多费事