使用mysql中的join语句获取最后插入的行

ilmyapht 于 2021-06-18 发布在 Mysql

关注(0)|答案(1)|浏览(361)

我正在尝试使用mysql和php创建一个简单的问题跟踪器。用户可以在 tracker 表，它将添加到 tracker 以及 statusmapping 马上上桌。
稍后，用户可以更改“状态”，然后将其添加（不更新）到 statusmapping table。
我想把这两个表合并起来得到输出。当我得到输出时，我想从 statusmapping 给定id的表。
我的 tracker table

+------------+---------+------------+----------------------+------------------------------------------------------+----------------------+
| 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 table。第二列 trackerid 是 FK 对于 tracker table。

+------------------+-----------+----------+---------------------+----------------------+
| 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!        |
+------------------+-----------+----------+---------------------+----------------------+

到目前为止我试过这个。我要走了 NULL 对于列 statusid . 但最后插入的id是 2 在 statusmapping table。我该怎么做呢。

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 |
+------------+----------------------+------------------------------------------------------+----------+

mysql Database JoinTable

来源：https://stackoverflow.com/questions/52801401/get-the-last-inserted-row-using-join-statement-in-mysql

1条答案

按热度按时间

oaxa6hgo1#

在单独的派生表中，可以获取 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

赞(0）回复(0）举报 2021-06-18

我来回答

使用mysql中的join语句获取最后插入的行

1条答案

相关问题

热门标签

最新问答