decode的一些用法和AVG空值问题：

botang

1. select  * from employees;
   
2. 
   
3. select count(*) from employees where to_char(hire_date,'YYYY')=2001
   
4. union all
   
5. select count(*) from employees where to_char(hire_date,'YYYY')=2002;
   
6. 
   
7. select  to_char(hire_date,'YYYY'),  count(*)  from employees
   
8.   group by to_char(hire_date,'YYYY') order by 1;
   
9. --------
   
10. select    sum(decode (  to_char(hire_date,'YYYY'),  2001, 1, 0) )  "Year2001",
    
11.              sum(decode (  to_char(hire_date,'YYYY'),  2002, 1, 0) )  "Year2002",
    
12.              sum(decode (  to_char(hire_date,'YYYY'),  2003, 1, 0) )  "Year2003",
    
13.               sum(decode (  to_char(hire_date,'YYYY'),  2004, 1, 0) )  "Year2004",
    
14.              sum(decode (  to_char(hire_date,'YYYY'),  2005, 1, 0) )  "Year2005",
    
15.              sum(decode (  to_char(hire_date,'YYYY'),  2006, 1, 0) )  "Year2006",
    
16.               sum(decode (  to_char(hire_date,'YYYY'),  2007, 1, 0) )  "Year2007",
    
17.              sum(decode (  to_char(hire_date,'YYYY'),  2008, 1, 0) )  "Year2008"
    
18.   from employees;

复制代码

以下两个计算平均提成，错误的例子:

1. select sum(commission_pct)/count(*) from employees;

复制代码

1. SELECT AVG(NVL(commission_pct, 0))  from
   
2. employees;

复制代码