我正在尝试使用mysql和php创建一个简单的问题跟踪器。
用户可以在tracker表中输入详细信息,它将立即添加到trackerstatusmapping表中。
稍后,用户可以更改“状态”,然后将其添加(未更新)到statusmapping表中。
我想合并这两个表并得到输出。当我得到输出时,我想从给定id的statusmapping表中获取最后插入的数据。
我的tracker

+------------+---------+------------+----------------------+------------------------------------------------------+----------------------+
| tracker_id | tasksid | pagenameid | tracker_summary      | tracker_comment                                      | tracker_created_date |
+------------+---------+------------+----------------------+------------------------------------------------------+----------------------+
|         14 |       2 |          8 | some summary         | some comment to display for the buggy page.          | 2018-10-14 13:05:31  |
|         15 |       4 |          4 | revision for the faq | revision for the faq page and the comment goes here. | 2018-10-14 14:09:27  |
+------------+---------+------------+----------------------+------------------------------------------------------+----------------------+

statusmapping表。第二列trackeridFK表的tracker
+------------------+-----------+----------+---------------------+----------------------+
| statusmapping_id | trackerid | statusid | statusmapping_date  | statusmapping_reason |
+------------------+-----------+----------+---------------------+----------------------+
|                4 |        14 |        1 | 2018-10-14 13:05:31 | Newly opened!        |
|                5 |        14 |        2 | 2018-10-14 13:34:04 | This issue is closed |
|                6 |        15 |        1 | 2018-10-14 14:09:27 | Newly opened!        |
+------------------+-----------+----------+---------------------+----------------------+

到目前为止我试过这个。我得到了列NULLstatusid。但最后插入的id是2表中的statusmapping。我怎么知道。
SELECT
trk.tracker_id,
trk.tracker_summary, trk.tracker_comment,
stm.statusid
FROM trackers trk
LEFT JOIN statusmapping stm ON trk.tracker_id = (SELECT MAX(trackerid) FROM `statusmapping`)
GROUP BY trk.tracker_id

这是输出。
+------------+----------------------+------------------------------------------------------+----------+
| tracker_id | tracker_summary      | tracker_comment                                      | statusid |
+------------+----------------------+------------------------------------------------------+----------+
|         14 | some summary         | some comment to display for the buggy page.          |     NULL |
|         15 | revision for the faq | revision for the faq page and the comment goes here. |        1 |
+------------+----------------------+------------------------------------------------------+----------+

最佳答案

在单独的Derived Table中,您可以获得tracker_id的最后更新日期时间。使用该值连接到主表,以获取tracker_id的最后一个条目。
请尝试以下操作:

SELECT
  trk.tracker_id,
  trk.tracker_summary,
  trk.tracker_comment,
  stm.statusid
FROM trackers trk
INNER JOIN statusmapping stm
  ON trk.tracker_id = stm.trackerid
INNER JOIN (SELECT trackerid,
                  MAX(statusmapping_date) AS max_statusmapping_date
           FROM statusmapping
           GROUP BY trackerid) AS dt
  ON dt.trackerid = trk.tracker_id AND
     dt.max_statusmapping_date = stm.statusmapping_date

10-07 16:35