mysql实现oracle lag功能

create table test_table(id int,name varchar(30));
insert into test_table select 1,'a1';
insert into test_table select 2,'a2';
insert into test_table select 3,'a3';
insert into test_table select 4,'a4';
insert into test_table select 5,'a5';
insert into test_table select 6,'a6';
insert into test_table select 7,'a7';

mysql> select @p_id p_id,@p_name p_name,t.id,t.name,(@p_id:=t.id) ,(@p_name:=t.name) from 
    -> (select * from test_table order by id,name) t,(select @p_id:=0,@p_name:=null) r;
+------+--------+------+------+---------------+-------------------+
| p_id | p_name | id   | name | (@p_id:=t.id) | (@p_name:=t.name) |
+------+--------+------+------+---------------+-------------------+
|    0 | NULL   |    1 | a1   |