使用INNER JOIN返回第二个表的最后一个条目

This is my first question here, so I'll try to keep it simple.

这是我的第一个问题,所以我会尽量保持简单。

I have two tables on my "Pendencies System":

我的“Pendencies系统”上有两张桌子:

1st - Main information (such as id, sender, priority...)
2nd - Logs of the 1st table (everything that users changes, like title, period...)

1st - 主要信息(例如id,发件人,优先级......)第2个 - 第1个表的日志(用户更改的所有内容,如标题,期间......)

I need to show every pendency, linked with the last log of the 2nd table, to get updated info.

我需要显示与第二个表的最后一个日志相关联的每个未决,以获取更新的信息。

How do I do that?

我怎么做?

Thanks in advance!

提前致谢!

2 个解决方案

#1

SELECT * FROM 
  Main JOIN
  (SELECT * FROM
     Logs where Logs.Id in 
        (SELECT MAX(Id) from Logs GROUP BY Logs.Main_Id)
  ) as newestLogs
  ON newestLogs.Main_Id = Main.Id

This should approximately work

这应该大致有效

See http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/

for an analysis of how to do the subtable query the fastest.

用于分析如何以最快的速度执行子表查询。

#2

You need to create a subQuery to gather the ID and max value from your logs table. Something like below should suffice.

您需要创建一个子查询来从日志表中收集ID和最大值。像下面这样的东西就足够了。

SELECT m.id, m.sender, m.priority, l2.*
FROM maininfo m
    INNER JOIN (SELECT id, MAX(PrimaryKey) AS Max
                FROM logs
                GROUP BY id) l ON m.id = l.id
    INNER JOIN logs l2 ON m.id = l2.id AND l.Max = l2.PrimaryKey

#1

SELECT * FROM 
  Main JOIN
  (SELECT * FROM
     Logs where Logs.Id in 
        (SELECT MAX(Id) from Logs GROUP BY Logs.Main_Id)
  ) as newestLogs
  ON newestLogs.Main_Id = Main.Id