SELECT ename, salary sal1,
LEAD(salary, 1) OVER (ORDER BY hiredate) AS sal2,
LEAD(salary, 1) OVER (ORDER BY hiredate) AS sal2,
LEAD(salary, 2) OVER (ORDER BY hiredate) AS sal3,
LEAD(salary, 3) OVER (ORDER BY hiredate) AS sal4,
LEAD(salary, 4) OVER (ORDER BY hiredate) AS sal5,
LEAD(salary, 5) OVER (ORDER BY hiredate) AS sal6,
LEAD(salary, 6) OVER (ORDER BY hiredate) AS sal7,
sal1+sal2+sal3+sal4+sal5+sal6+sal7 sum_salary,
sum_salay/7 avg_salaay
FROM emp;
可以使用这种办法
借助lead和lag函数
LEAD(salary, 1) OVER (ORDER BY hiredate) AS sal2,
LEAD(salary, 1) OVER (ORDER BY hiredate) AS sal2,
LEAD(salary, 2) OVER (ORDER BY hiredate) AS sal3,
LEAD(salary, 3) OVER (ORDER BY hiredate) AS sal4,
LEAD(salary, 4) OVER (ORDER BY hiredate) AS sal5,
LEAD(salary, 5) OVER (ORDER BY hiredate) AS sal6,
LEAD(salary, 6) OVER (ORDER BY hiredate) AS sal7,
sal1+sal2+sal3+sal4+sal5+sal6+sal7 sum_salary,
sum_salay/7 avg_salaay
FROM emp;
可以使用这种办法
借助lead和lag函数
----------- ---------- ------------------------------
2006-2-2 1 1
2006-2-3 2 1.5
2006-2-4 3 2
2006-2-5 4 2.5
2006-2-6 5 3
2006-2-10 9 5.25
2006-2-11 10 7
2006-2-12 11 8.75
2006-2-13 12 10.5
2006-2-14 13 11
2006-2-15 14 11.5
2006-2-16 9 11.1428571428571
2006-2-17 10 11.2857142857143
2006-2-22 11 10
2006-2-23 12 11
2006-3-2 13 13
2006-3-5 14 13.5