oracle获取表前几行,Oracle查询以获取同一表中相关行的先前值

我有一张桌子学生,名字和评分年份明智.

Name Year Rating

Ram 2016 10

Sam 2016 9

Ram 2014 8

Sam 2012 7

我需要找到员工之前的评级,可能是去年或几年前.

查询应返回以下结果

Name Cur_rating_year_2016 Prev_rating

Ram 10 8

Sam 9 7

下面是insert和create的脚本

Create table Student (name varchar2(10), year number, rating number );

insert into student values('Ram' ,2016 ,10);

insert into student values('Sam' ,2016 ,9);

insert into student values('Sam' ,2012 ,7);

insert into student values('Ram' ,2014 ,8);

有没有办法使用选择查询来实现这一目标？

LAG is an analytic function. It provides access to more than one row

of a table at the same time without a self join. Given a series of

rows returned from a query and a position of the cursor, LAG provides

access to a row at a given physical offset prior to that position.

For the optional offset argument, specify an integer that is greater

than zero. If you do not specify offset, then its default is 1. The

optional default value is returned if the offset goes beyond the scope

of the window. If you do not specify default, then its default is

null.

SELECT stud_name AS name,

r_year AS year,

r_value AS rating,

lag(r_value, 1, NULL) OVER(PARTITION BY stud_name ORDER BY r_year) AS prev_rating

FROM stud_r

ORDER BY stud_name;