用一句SQL将下表搜索成结果表:表结构:year month sal
2005 1 1000
2005 2 2000
2005 3 3000
2005 4 4000
2006 1 5000
2006 2 6000
2006 3 7000
2006 4 8000搜索出如下表:
year m1 m2 m3 m4
2005 1000 2000 3000 4000
2006 5000 6000 7000 8000
2005 1 1000
2005 2 2000
2005 3 3000
2005 4 4000
2006 1 5000
2006 2 6000
2006 3 7000
2006 4 8000搜索出如下表:
year m1 m2 m3 m4
2005 1000 2000 3000 4000
2006 5000 6000 7000 8000
sum(decode(month,1,sal,0)) m1,
sum(decode(month,2,sal,0)) m2,
sum(decode(month,3,sal,0)) m3,
sum(decode(month,4,sal,0)) m4
from table
group by year;
select emp_year,
max(decode(emp_month,1,to_char(emp_sal,'9.9'),'')),
max(decode(emp_month,2,to_char(emp_sal,'9.9'),'')),
max(decode(emp_month,3,to_char(emp_sal,'9.9'),'')),
max(decode(emp_month,4,to_char(emp_sal,'9.9'),''))
from emp
group by emp_year;