decode的一些用法和AVG空值问题：
select* from employees;select count(*) from employees where to_char(hire_date,'YYYY')=2001
union all
select count(*) from employees where to_char(hire_date,'YYYY')=2002;
selectto_char(hire_date,'YYYY'),count(*)from employees
group by to_char(hire_date,'YYYY') order by 1;
--------
select sum(decode (to_char(hire_date,'YYYY'),2001, 1, 0) )"Year2001",
sum(decode (to_char(hire_date,'YYYY'),2002, 1, 0) )"Year2002",
sum(decode (to_char(hire_date,'YYYY'),2003, 1, 0) )"Year2003",
sum(decode (to_char(hire_date,'YYYY'),2004, 1, 0) )"Year2004",
sum(decode (to_char(hire_date,'YYYY'),2005, 1, 0) )"Year2005",
sum(decode (to_char(hire_date,'YYYY'),2006, 1, 0) )"Year2006",
sum(decode (to_char(hire_date,'YYYY'),2007, 1, 0) )"Year2007",
sum(decode (to_char(hire_date,'YYYY'),2008, 1, 0) )"Year2008"
from employees;以下两个计算平均提成，错误的例子:
select sum(commission_pct)/count(*) from employees;
SELECT AVG(NVL(commission_pct, 0))from
employees;
页:
[1]