CASE语句优化SQL统计-优快云博客

例子：统计employees表中2000以下，2000-4000，4000以上范围内各自的工资总额，按照常规写法，需要写3条sql，分别统计这三个范围内的工资总和。
使用case语句，可以用一条sql完成，且相应的资源消耗也降低。

常规写法：
select sum(salary) from employees where salary<2000 ;
select sum(salary) from employees where salary between 2001 and 4000;
select sum(salary) from employees where salary>4001;

使用case的写法：
select sum (case when salary<2000
                     then salary else null end) sum1,
          sum (case when salary between 2001 and 4000
                     then salary else null end) sum2,
          sum (case when salary >4000
                    then salary else null end) sum3
from employees;

对比各自产生的逻辑读数量：
SQL> select sum(salary) from employees where salary between 2001 and 4000;

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          4 consistent gets
          0 physical reads
          0 redo size
        415 bytes sent via SQL*Net to client
        395 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL> select sum (case when salary<2000
                     then salary else null end) sum1,
          sum (case when salary between 2001 and 4000
                     then salary else null end) sum2,
          sum (case when salary >4000
                    then salary else null end) sum3
from employees; 2    3    4    5    6    7

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          4 consistent gets
          0 physical reads
          0 redo size
        521 bytes sent via SQL*Net to client
        395 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed
两次执行消耗的逻辑读数量都是4，而第一条sql需要执行3次才能得到想要的结果。显然，使用case的情况下，消耗的逻辑读总量只有常规情况下的1/3。