减少对表的扫描次数，将大大有利于执行的效率

例子1(低效):

select z.dept_name

from(select item_key,dept_name,min(b.begin_date) t1

from table1 a,table2 b

where activity_name='1';

and .....

group by ...) z,

(select item_key,min(b.begin_date) t2

from table1 a, table2 b

where activity_name='2'

and....

group by....) w,

(select item_key,max(b.begin_date) t3,min(b.end_date) t4

from table1 a, table2 b

where activity_name='3'

and.....

group by.....)Q

where z.item_key = w.item_key

and w.item_key = Q.item_key

例子1的(高效)：

select dept_name,

max(T1) T1,

max(T2)T2

from (select dept_name,

(case

when activity_name = '1') then min(a.begin_date) else null end) t1,

(case

when activity_name='2') then min(a.end_date) else null end) t2

from table1 a, table2 b

where activity_name in('1','2');

and ......

and.....

说明：有些情况下，可以用case语句来提高效率。

例子二(高效)(case when 后面可接条件表达式,可以用于统计数量，效率还可以):

select t.dw,sum(case when exists(select 1 from table2 a

where a...= t....) and t...

then '1'

else '0' end ) 数量1,

sum(case when exists(select 1 from tabble 2 a

where a... = t...) and t...

then '1'

else '0' end) 数量2

from table t

where t.org_no like ''

and t.....

group by t.org_no

例子二(比前者低效)

select dw, sum(case when xm='数量1' then sl else 0 end)数量1,

sum(case when xm='数量2' then sl else 0 end)数量2

from (select t.org_no dw,'数量1' xm,count(*) sl

from table t

where t....

and t......
and exists(select 1 from table2 a

where a......)

group by t.org_no

union

select t.org_no dw, '数量2' xm,count(*) sl

from table t

where t.....

and exists(select 1 from table2 a

where a....)

group by t.org_no)

group by dw