php - MySQL加入另一个表

我有2张桌子如下::

订单表::

order_id    |    store_id    |  member_id    |     create_tstamp
  90                2              15             28/07/2014  15:35:00
  91                2              16             29/07/2014  16:11:12
  92                2              15             30/07/2014  08:07:12
  93                2              15             01/08/2014  09:33:12

transferPayment表::

transfer_id    | order_id  |  amount  |   transfer_time      |  create_tstamp
   1               90         1000      30/07/2014 11:00:00   30/07/2014 12:00:00
   2               90         800       30/07/2014 11:00:00   30/07/2014 13:14:56
   3               90         956       30/07/2014 11:00:00   30/07/2014 14:16:17
   4               91         385       01/08/2014 14:35:00   01/08/2014 16:18:17
   5               91         390       01/08/2014 14:35:00   01/08/2014 16:30:17
   6               92         198       05/08/2014 15:11:11   06/08/2014 17:18:18

我想加入两个表。条件是订单表多是与transferPayment表中具有order_id和create_tstamp相同的transferPayment表的连接。

最后。结果如下：

order_id    |    store_id    |  member_id    |     create_tstamp   | transfer_id  | amount |  transfer_time     | create_tstamp
  90               2             15            28/07/2014 15:35:00        3          956   30/07/2014 11:00:00   30/07/2014 13:14:56
  91               2             16            29/07/2014  16:11:12       5          390   01/08/2014 14:35:00   01/08/2014 16:30:17
  92               2             15            30/07/2014  08:07:12       6          198   05/08/2014 15:11:11   06/08/2014 17:18:18

什么是MySQL命令来解决这个问题？

最佳答案

要获取最新的transferPayment，您必须使用max(transfer_id)和group by order_id
像下面的查询也许？

SELECT o.order_id,o.store_id,o.member_id,o.create_tstamp as order_create_tstamp,
       t.transfer_id,t.amount,t.transfer_time,t.create_tstamp as transfer_create_tstamp
FROM
  transferPayment t
INNER JOIN
  (SELECT max(transfer_id) as transfer_id,order_id
   FROM transferPayment
   GROUP BY order_id) t2
ON t.transfer_id = t2.transfer_id AND t.order_id = t2.order_id
INNER JOIN
  `order` o
ON o.order_id = t.order_id

这是sqlfiddle demo