SQL Tips N-1のデータを取得する

実行環境：MySQL 5.1

select * from items;
+------+------------+-------+
| code | date       | price |
+------+------------+-------+
| 001  | 2014-01-01 |   100 |
| 001  | 2014-01-02 |   101 |
| 001  | 2014-01-04 |   102 |
| 001  | 2014-01-05 |   103 |
| 002  | 2014-01-01 |   200 |
| 002  | 2014-01-02 |   201 |
| 002  | 2014-01-04 |   202 |
| 002  | 2014-01-05 |   203 |
+------+------------+-------+

select a.code, a.date, b.date preDate, a.price, b.price as prePrice
from item a, item b
where 
       a.code = b.code
 and b.date = (select max(date) from item where date < a.date and code = a.code)
+------+------------+------------+-------+----------+
| code | date       | preDate    | price | prePrice |
+------+------------+------------+-------+----------+
| 001  | 2014-01-02 | 2014-01-01 |   101 |      100 |
| 001  | 2014-01-04 | 2014-01-02 |   102 |      101 |
| 001  | 2014-01-05 | 2014-01-04 |   103 |      102 |
| 002  | 2014-01-02 | 2014-01-01 |   201 |      200 |
| 002  | 2014-01-04 | 2014-01-02 |   202 |      201 |
| 002  | 2014-01-05 | 2014-01-04 |   203 |      202 |
+------+------------+------------+-------+----------+

準備用のScript

#!/bin/sh
 q(){
     mysql -uroot -proot -Dtest_db -e "$*"
 }
 
 q "drop table item"
 q "create table item(code varchar(10), date date, price int(10))"
 q "insert into item (code, date, price) values ('001','2014-01-01', 100)"
 q "insert into item (code, date, price) values ('001','2014-01-02', 101)"
 q "insert into item (code, date, price) values ('001','2014-01-04', 102)"
 q "insert into item (code, date, price) values ('001','2014-01-05', 103)"
 q "insert into item (code, date, price) values ('002','2014-01-01', 200)"
 q "insert into item (code, date, price) values ('002','2014-01-02', 201)"
 q "insert into item (code, date, price) values ('002','2014-01-04', 202)"
 q "insert into item (code, date, price) values ('002','2014-01-05', 203)"