64.Oracle数据库SQL开发之 高级查询——使用LAG和LEAD函数
欢迎转载,转载请标明出处:http://blog.csdn.net/notbaron/article/details/49847059
LAG和LEAD函数可获得位于距当前记录指定距离处的那条记录中的数据。
查询用LAG和LEAD获得前一个月和后一个月的销量。
store@PDB1> select month,sum(amount) asmonth_amount,lag(sum(amount),1) over (order by month) as previous_month_amount,
lead(sum(amount),1)over (order by month) as next_month_amount
fromall_sales
whereyear=2003 group by month order by month;
MONTH MONTH_AMOUNT PREVIOUS_MONTH_AMOUNTNEXT_MONTH_AMOUNT
---------- --------------------------------- -----------------
1 95525.55 116671.6
2 116671.6 95525.55 160307.92
3 160307.92 116671.6 175998.8
4 175998.8 160307.92 154349.44
5 154349.44 175998.8 124951.36
6 124951.36 154349.44 170296.16
7 170296.16 124951.36 212735.68
8 212735.68 170296.16 199609.68
9 199609.68 212735.68 264480.79
10 264480.79 199609.68 160221.98
11 160221.98 264480.79 137336.17
12 137336.17 160221.98
12 rows selected.
LAG(SUM(amount),1)可获取前一条记录的数量总计。