mysql - 按日期获取每个类别过滤器的最后一条记录

Question

我有这个表SQL Fiddle

项目表:

+----+----------+
| id |   name   |
+----+----------+
|  1 | Facebook |
|  2 | Twitter  |
|  3 | Amazon   |
+----+----------+

价格表:

+----+-----------+---------+-----------------------------+
| id |    buy    | item_id |             created_at      |
+----+-----------+---------+-----------------------------+
|  1 |   43000   |    1    | June, 18 2014 17:31:04+0000 |
|  2 |   44000   |    1    | June, 19 2014 17:31:04+0000 |
|  3 |   30000   |    2    | June, 20 2014 17:31:04+0000 |
|  4 |   33000   |    2    | June, 21 2014 17:31:04+0000 |
|  5 |   20000   |    3    | June, 22 2014 17:31:04+0000 |
|  6 |   21000   |    3    | June, 23 2014 17:31:04+0000 |
+----+-----------+---------+-----------------------------+

我想根据价格日期获取每件商品的最后价格以及最后价格购买字段之前的价格

期望的输出:

+----+---------+-----------------+---------+
| id |   buy   | last_before_buy | item_id |
+----+---------+-----------------+---------+
| 10 |  45000  |     43000       |    3    |
| 7  |  33000  |     31000       |    2    |
| 4  |  23000  |     23000       |    1    |
+----+---------+-----------------+---------+

Answer 1

这是另一种方法:

select a.id, a.buy, b.buy last_before_buy, a.item_id
from (select * from prices WHERE (created_at <= NOW() - INTERVAL 5 DAY) order by id desc) a
join (select * from prices order by id desc) b on a.item_id = b.item_id and a.id > b.id
group by a.item_id;

fiddle