Oracle笔记之二(DECODE以及常用窗口函数)

 10.利用DECODE进行行列转换
 select '工资' 部门,
 sum(decode(k.deptno,'10',k.g)) A
 ,sum(decode(k.deptno,'20',k.g)) B
 ,sum(decode(k.deptno,'30',k.g)) C
 ,sum(decode(k.deptno,'40',k.g)) D
 from (select sum(e.sal) g,e.deptno from emp e
 left join dept d on d.deptno=e.empno
  group by e.deptno) k;
11.Oracle 窗口函数
 rank() over(order by emp_age)
 dense_rank() over(order by emp_age)
 row_number() over(order by emp_age)
 avg(age) over(partition by position) avg
 sum(age) over(order by age rows between 1 preceding and 1 following)--获取当前记录的前一行以及后一行
 count(1) over(order by age range betwwen 1 preceding and 1 following) c--差在一岁的数据