我有一个这样的查询语句:
select
(select sum(qty) from c_tb_itemdeman where date_y < plan_y and plan_y = 2012 and c_tb_itemdeman.itemno = itemdata.itemno ) n_qty1,
(select sum(productionqty - inqty)
from c_tb_rollplan
where planclass in('01','02','09','08','06') and roll_y = 2011
and planState = 3
and c_tb_rollplan.itemno = itemdata.itemno ) y_qty1
from itemdata
基表itemdata 表中 150多条数据 c_tb_itemdeman中有 1w多条数据,c_tb_rollplan表中有500条数据左右
上把的sql语句在 2hz,2g内存的服务器上测试运行花了 20秒时间左右
但是将这条sql语拆分成如下sql语句执行时,花的时间都在1秒左右,请高手优化一下
拆分1:
select
(select sum(qty) from c_tb_itemdeman where date_y < plan_y and plan_y = 2012 and c_tb_itemdeman.itemno = itemdata.itemno ) n_qty1,
-- (select sum(productionqty - inqty)
-- from c_tb_rollplan
-- where planclass in('01','02','09','08','06') and roll_y = 2011
-- and planState = 3
-- and c_tb_rollplan.itemno = itemdata.itemno ) y_qty1
from itemdata拆分2:
select
--(select sum(qty) from c_tb_itemdeman where date_y < plan_y and plan_y = 2012 and c_tb_itemdeman.itemno = itemdata.itemno ) n_qty1,
(select sum(productionqty - inqty)
from c_tb_rollplan
where planclass in('01','02','09','08','06') and roll_y = 2011
and planState = 3
and c_tb_rollplan.itemno = itemdata.itemno ) y_qty1
from itemdata
select
(select sum(qty) from c_tb_itemdeman where date_y < plan_y and plan_y = 2012 and c_tb_itemdeman.itemno = itemdata.itemno ) n_qty1,
(select sum(productionqty - inqty)
from c_tb_rollplan
where planclass in('01','02','09','08','06') and roll_y = 2011
and planState = 3
and c_tb_rollplan.itemno = itemdata.itemno ) y_qty1
from itemdata
基表itemdata 表中 150多条数据 c_tb_itemdeman中有 1w多条数据,c_tb_rollplan表中有500条数据左右
上把的sql语句在 2hz,2g内存的服务器上测试运行花了 20秒时间左右
但是将这条sql语拆分成如下sql语句执行时,花的时间都在1秒左右,请高手优化一下
拆分1:
select
(select sum(qty) from c_tb_itemdeman where date_y < plan_y and plan_y = 2012 and c_tb_itemdeman.itemno = itemdata.itemno ) n_qty1,
-- (select sum(productionqty - inqty)
-- from c_tb_rollplan
-- where planclass in('01','02','09','08','06') and roll_y = 2011
-- and planState = 3
-- and c_tb_rollplan.itemno = itemdata.itemno ) y_qty1
from itemdata拆分2:
select
--(select sum(qty) from c_tb_itemdeman where date_y < plan_y and plan_y = 2012 and c_tb_itemdeman.itemno = itemdata.itemno ) n_qty1,
(select sum(productionqty - inqty)
from c_tb_rollplan
where planclass in('01','02','09','08','06') and roll_y = 2011
and planState = 3
and c_tb_rollplan.itemno = itemdata.itemno ) y_qty1
from itemdata
类似
select * from itemdata,c_tb_itemdeman group by ...
感觉应该是这样
c_tb_rollplan.itemno(+) = itemdata.itemno 而且你写的方式不太合乎正常SQL,哈哈
我现在的方式大概是这样的:
select
(在c_tb_itemdeman表中统计数据)as n_qty1,
(在c_tb_rollplan表中统计数据) as y_qty1,
......,
...
from itemdata 基表