MySQL子查询 - 在LEFT JOIN中仅查找第一条记录

Question

我正在尝试显示一组成员记录，为此使用了几个表来显示所需的内容。

这是很容易的部分。我需要帮助的部分是有一个表格有许多记录与每个成员记录相关联：登录历史记录。

我想仅显示存在于登录历史记录表中的每个成员记录的第一行。或者，我可能只想交换并显示登录历史表中的最后一条记录。

以下是我迄今为止所做的：

SELECT m.memberid, m.membername, m.gender, mp.phone
FROM tbl_members m, 
     tbl_members_phones mp, 
     tbl_members_addresses ma
WHERE m.defaultphoneid = mp.phoneid
AND m.defaultaddressid = ma.addressid

这样可以返回预期的结果。

我想在返回的结果中添加来自tbl_members_login_history的两列： mh。loggedtime，mh。ipaddy

我知道将tbl_members_login_history添加为LEFT JOIN会返回重复项，所以我认为这里必须有一个子查询来仅返回存在于tbl_members_login_history中的那个memberid的第一个记录。

我担心的是如果历史记录表中没有记录，我仍然希望显示该会员信息，但将历史记录列设置为NULL。

这是否是一个子查询问题？如果是，如何添加这种类型的LIMIT？

- coffeemonitor

---

出于好奇，成员需要电话和地址吗？如果不需要，我认为你的查询将无法返回其他成员信息，如果其中一个信息缺失，就像它现在被写的那样。 - Sam

在这种情况下，是的。但你说得对，如果没有现有记录，该成员将不会返回在结果中。 - coffeemonitor

Answer 1

这是stackoverflow上经常被问到的“每组中的最佳n个元素”问题。

以下是我在您的情况下如何解决它：

SELECT m.memberid, m.membername, m.gender, mp.phone, mh.loggedtime, mh.ipaddy
FROM tbl_members m 
INNER JOIN tbl_members_phones mp ON m.defaultphoneid = mp.phoneid
INNER JOIN tbl_members_addresses ma ON m.defaultaddressid = ma.addressid
LEFT OUTER JOIN tbl_members_login_history mh ON m.memberid = mh.memberid
LEFT OUTER JOIN tbl_members_login_history mh2 ON m.memberid = mh2.memberid
    AND mh.pk < mh2.pk
WHERE mh2.pk IS NULL;

那么，我们希望 mh 是给定 memberid 最近一次在 tbl_member_login_history 中的行。因此，我们搜索另一行 mh2，它更加最近。如果没有比 mh 行更近的，则 mh2.* 将为 NULL，因此 mh 必须是最近的。

我假设这个表有一个包含递增值的主键列。对于这个示例，我假设列名为 pk。

使用 LEFT OUTER JOIN 在登录历史表中引用 两个 引用意味着即使没有匹配的行，也将报告 m 行。

- Bill Karwin

---

我刚刚尝试了你的解决方案，并将其与包含子查询的使用GROUP BY和MAX（...）的查询进行了比较，连接ON date = maxdate。您的解决方案使用<运算符花费了2秒钟，而子查询解决方案只花费了0.01秒钟（当然是相同的结果表）。您的查询的EXPLAIN看起来比具有子查询的那个好得多。我不知道为什么会出现如此巨大的性能差异，但我猜测是因为<产生了巨大的中间结果。 - steffen

@steffen：是的，自从我回答了这个问题以后，我看到了很多其他情况，并得出结论，上面的解决方案或者像你描述的解决方案可以更快，具体取决于数据有多少个不同的组和每个组有多少行。因此最好用您的数据测试两种方法，然后再决定。 - Bill Karwin

真相是。让我困惑的是巨大的性能差异。我在这里几次找到了你的解决方案。并且“EXPLAIN”输出看起来很好，所以我期望这个查询非常快。但事实上它比较慢。很奇怪。 - steffen

@steffen，请记住MySQL支持嵌套循环连接。因此，您可以通过将每个表的“行”信息相乘来大致估计执行连接所需的工作量。因此，即使看起来它正在使用索引和其他内容，如果您看到一个表中的几千行与另一个表中的几千行连接，它实际上正在进行数百万行比较。 - Bill Karwin

Answer 2

像这样添加

LEFT OUTER JOIN (SELECT member_id, MAX(LAST_LOGIN_DATE) from tbl_members_login_history) Last_Login ON Last_Login.memberid = m.memberid

PS. LAST_LOGIN_DATE 是伪列，您可以尝试使用您的限制列

- kochobay