GBASE南大通用分享
原SQL：
select count('x') rs
from ab01 a, ac02 b
where a.aab001 = b.aab001
and b.aae140 = '110'
and a.aab301 = '1001'
and exists(select 'x' from ic10 c where b.aac001 = c.aac001 and c.aae140='110')
and exists(select 'x' from ac01 d where b.aac001 = d.aac001 and d.aac004 = '2' ) ;
修改后：
select count(*) from (select b.aac001 as aac001 from ab01 a, ac02 b
where a.aab001 = b.aab001
and b.aae140 = '110'
and a.aab301 = '1001'
and exists(select 'x' from ic10 c where b.aac001 = c.aac001 and c.aae140='110')) tt
join ac01_1 d where tt.aac001 = d.aac001 and d.aac004 = '2';
rsSQL1：
select
distinct cert.emp_id
from
cm_log cl
inner join
(
select
emp.id as emp_id,
emp_cert.id as cert_id
from
employee emp
left join
emp_certificate emp_cert
on emp.id = emp_cert.emp_id
where
emp.is_deleted=0
) cert
on (
cl.ref_table='Employee'
and cl.ref_oid= cert.emp_id
)
or (
cl.ref_table='EmpCertificate'
and cl.ref_oid= cert.cert_id
)
where
cl.last_upd_date >='2013-11-07 15:03:00'
and cl.last_upd_date<='2013-11-08>
基本思想是如果cm_log的ref_table是EmpCertificate就关联emp_certificate表，如果ref_table是Employee就关联employee表，我们完全可以拆成两部分，并用union连接起来，注意这里用union，而不用union all是因为原语句有“distinct”来得到唯一的记录，而union恰好具备了这种功能。如果原语句中没有distinct不需要去重，我们就可以直接使用union all了，因为使用union需要去重的动作，会影响SQL性能。
优化过的语句如下
select
emp.id
from
cm_log cl
inner join
employee emp
on cl.ref_table = 'Employee'
and cl.ref_oid = emp.id
where
cl.last_upd_date >='2013-11-07 15:03:00'
and cl.last_upd_date<='2013-11-08>
and emp.is_deleted = 0
union
select
emp.id
from
cm_log cl
inner join
emp_certificate ec
on cl.ref_table = 'EmpCertificate'
and cl.ref_oid = ec.id
inner join
employee emp
on emp.id = ec.emp_id
where
cl.last_upd_date >='2013-11-07 15:03:00'
and cl.last_upd_date<='2013-11-08>
and emp.is_deleted = 0;
