二個SQL tuning例子(使用case)

減少對錶的掃描次數，將大大有利於執行的效率

例子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

[@more@]