2013/05/20 09:13

select deptno, sal from scott.emp order by deptno
DEPTNO SAL
10 2450.00
10 5000.00
10 1300.00
20 2975.00
20 3000.00
20 1100.00
20 800.00
20 3000.00
30 1250.00
30 1500.00
30 1600.00
30 950.00
30 2850.00
30 1250.00
ok那现在比方我有个这样的需求（我们只看sal列）我想问你2450的上一个值是多少？回答是没有 那5000的上一个值是多少？是：2450 1300的上一个值是多少呢？是：5000 Ok以此类推我想得到当前值的上一个值

5000.00 2450
1300.00 5000
2975.00 1300
3000.00 2975
1100.00 3000
...       ...
1250.00   2850
OK就这样的需求 那我们现在用SQL应该如何写呢？是的你猜对了就是用lag分析函数:
select deptno, sal a, lag(sal, 1, sal) b over(order by deptno)
from scott.emp
DEPTNO A B
10 2450.00 2450 --ps:这里的之所以是2450是因为lag(sal, 1, sal)我让它给了他本身的值
10 5000.00 2450
10 1300.00 5000
20 2975.00 1300
20 3000.00 2975
20 1100.00 3000
20 800.00 1100
20 3000.00 800
30 1250.00 3000
30 1500.00 1250
30 1600.00 1500
30 950.00 1600
30 2850.00 950
30 1250.00 2850

select deptno, sal a, lead(sal, 1, sal) over(order by deptno) b
from scott.emp
DEPTNO A B
10 2450.00 5000
10 5000.00 1300
10 1300.00 2975
20 2975.00 3000
20 3000.00 1100
20 1100.00 800
20 800.00 3000
20 3000.00 1250
30 1250.00 1500
30 1500.00 1600
30 1600.00 950
30 950.00 2850
30 2850.00 1250
30 1250.00 1250

select deptno, sal a, lag(sal, 2，null) over(order by deptno) b
from scott.emp
DEPTNO A B
10 2450.00      --注意这里是null空了
10 5000.00
10 1300.00 2450  --A列1300的上两个值是多少？2450是吧
20 2975.00 5000
20 3000.00 1300
20 1100.00 2975
20 800.00 3000
20 3000.00 1100
30 1250.00 800
30 1500.00 3000
30 1600.00 1250
30 950.00 1500
30 2850.00 1600
30 1250.00 950
select deptno,
sal a,
lag(sal, 1, null) over( partition by deptno order by deptno) b
from scott.emp
DEPTNO A B
10 2450.00
10 5000.00 2450
10 1300.00 5000
20 2975.00
20 3000.00 2975
20 1100.00 3000
20 800.00 1100
20 3000.00 800
30 1250.00
30 1500.00 1250
30 1600.00 1500
30 950.00 1600
30 2850.00 950
30 1250.00 2850

0 评论
0 收藏
0