select a.*,b.nopassnum,c.returnnopassnum,c.nopassnum_relation,c.nopassnum_notrelation,dealmemo as dealmemox,
a.dealtime as dealtimex,a.dealman as dealmanx,a.FivedetectionResult as FivedetectionResult
from
(select Qm_receive_send_unit.*,qm_basic_outpartname.department,qm_basic_outpartname.partclass from Qm_receive_send_unit
left join qm_basic_outpartname on Qm_receive_send_unit.outpartname=qm_basic_outpartname.outpartname) a
left join
(select unitid,sum(nopassnum) as nopassnum from Qm_receive_select2_nopass group by unitid) b
on a.id=b.unitid
left join
(select unitid,sum(nopassnum) as returnnopassnum,
sum(case when ifrelation=1 then nopassnum else 0 end) as nopassnum_relation,
sum(case when ifrelation=0 then nopassnum else 0 end) as nopassnum_notrelation from Qm_receive_select2_recycle group by unitid) c
on a.id=c.unitid
where (senddate>='2011-09-01' and senddate<='2011-09-30')
a.dealtime as dealtimex,a.dealman as dealmanx,a.FivedetectionResult as FivedetectionResult
from
(select Qm_receive_send_unit.*,qm_basic_outpartname.department,qm_basic_outpartname.partclass from Qm_receive_send_unit
left join qm_basic_outpartname on Qm_receive_send_unit.outpartname=qm_basic_outpartname.outpartname) a
left join
(select unitid,sum(nopassnum) as nopassnum from Qm_receive_select2_nopass group by unitid) b
on a.id=b.unitid
left join
(select unitid,sum(nopassnum) as returnnopassnum,
sum(case when ifrelation=1 then nopassnum else 0 end) as nopassnum_relation,
sum(case when ifrelation=0 then nopassnum else 0 end) as nopassnum_notrelation from Qm_receive_select2_recycle group by unitid) c
on a.id=c.unitid
where (senddate>='2011-09-01' and senddate<='2011-09-30')
奇怪的是如果日期选的时间范围越长效率越高
最重要的一点我沉得是没有条件 只有一个时间条件,我没有看到那个表有个senddate字段,如果有子查询把这个条件放在子查询里面,不要放到最外面。
还要建索引。
select a.*,b.nopassnum,c.returnnopassnum,c.nopassnum_relation,c.nopassnum_notrelation,dealmemo as dealmemox,
a.dealtime as dealtimex,a.dealman as dealmanx,a.FivedetectionResult as FivedetectionResult
from
(select Qm_receive_send_unit.*,qm_basic_outpartname.department,qm_basic_outpartname.partclass from Qm_receive_send_unit
left join qm_basic_outpartname on Qm_receive_send_unit.outpartname=qm_basic_outpartname.outpartname
where (senddate>='2011-09-01' and senddate<='2011-09-30')
) a
left join
(select unitid,sum(nopassnum) as nopassnum from Qm_receive_select2_nopass group by unitid) b
on a.id=b.unitid
left join
(select unitid,sum(nopassnum) as returnnopassnum,
sum(case when ifrelation=1 then nopassnum else 0 end) as nopassnum_relation,
sum(case when ifrelation=0 then nopassnum else 0 end) as nopassnum_notrelation from Qm_receive_select2_recycle group by unitid) c
on a.id=c.unitid
senddate的索引情况你没有说明,数据量也没有说明
效率没提高,一样的。
数据量不大 ,几个表都不到7万条
sum(case when ifrelation=1 then nopassnum else 0 end) as nopassnum_relation,
sum(case when ifrelation=0 then nopassnum else 0 end) as nopassnum_notrelation from Qm_receive_select2_recycle group by unitid
from (不到1秒的sql)
where (senddate>='2011-09-01' and senddate<='2011-09-30')