MySQL 获取 : need some advice

Question

我想从单个表中获取信息，其中我将显示每条记录的最新交易，同时显示上一个交易的记录。

表:

|id | account_no | date1      | price |
|1  | 101        |2018-04-10  | 100   |
|2  | 101        |2018-04-7   | 200   |
|3  | 102        |2018-04-10  | 300   |

输出应如下所示:

|account_no|latest_entry(date)|price |previous_entry(date)|price |
|101       |2018-04-10        |100   |2018-04-7           |200   |
|102       |2018-04-10        |300   |null                |0     |

这是我的查询:

select max(date_recorded) as ldte, test_tbl.* from test_tbl group by account_no order by date_recorded desc

提前致谢。

Answer 1

您可以通过执行以下操作来获取先前信息的引用:

select t.*,
       (select t2.id
        from t t2
        where t2.account_no = t.account_no and t2.date1 < t.date1
        order by t2.date1 desc
        limit 1
       ) as prev_id
from t ;

使用它作为子查询来连接回原始表:

select t.*, tprev.date, tprev.price
from (select t.*,
             (select t2.id
              from t t2
              where t2.account_no = t.account_no and t2.date1 < t.date1
              order by t2.date1 desc
              limit 1
             ) as prev_id
      from t
     ) t join
     t tprev
     on t.prev_id = tprev.id;

您可以通过添加以下 where 子句将其限制为最近的日期:

where t.id = (select t2.id
              from t t2
              where t2.account_no = t.account_no
              order by t2.date desc
              limit 1
             )

这可以在外部查询或子查询中。