MySQL子查询-仅在LEFT JOIN中查找第一条记录 [英] MySQL subquery - Find only first record in a LEFT JOIN

问题描述

我正在尝试显示成员记录的列表，并且我有一些表用来显示所需的内容.

I'm trying to display a list of member records, and I have a few tables I'm using to display what I need.

那是容易的部分.我需要帮助的部分是一个表，该表对每个成员记录都有很多记录:登录历史记录

That's the easy part. The part I need help with is with a table that has many records to each member record: Login history

我只想显示登录历史记录表中存在的每个成员记录的第一行.另外，我可能还想触发器，并在登录历史记录"表中显示最后一条记录.

I want to display only the first row for each member record, that exists in the Login History table. Alternatively, I may want to flip flop and display the last record in the Login History table, as well.

这是到目前为止我得到的:

here's what I've got so far:

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中的2列是:mh.loggedtime，mh.ipaddy

The 2 columns from tbl_members_login_history I'd like to add to the returned result are: mh.loggedtime, mh.ipaddy

我知道将tbl_members_login_history添加为LEFT JOIN会返回重复项，因此我认为这里必须有子查询的必要性，以便仅返回存在于tbl_members_login_history中的该memberid的第一条记录. .

I know adding the tbl_members_login_history as a LEFT JOIN would return duplicates, so I'm thinking there must be a Subquery necessity here, in order to return just the 1st record for that memberid that exists in tbl_members_login_history.

我担心的是，如果历史记录表中不存在任何记录，我仍然想显示该成员信息，但将历史记录列保留为NULL.

What I'm worried about is if no record in the history table exists, I still want to display that member info, but leave the history columns as NULL.

这将是子查询事件吗?如果是这样，又该如何添加LIMIT类型呢?

Would this be a subquery incident? and if so, how does one add that type of LIMIT?

推荐答案

这是greatest-n-per-group问题，在堆栈溢出中经常被问到.

This is the greatest-n-per-group problem, which is asked frequently on Stack Overflow.

在您的情况下，我将按照以下方法解决该问题:

Here's how I would solve it in your scenario:

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是给定成员ID的tbl_member_login_history中的最新行.因此，我们搜索了最近的另一行mh2.如果没有找到比mh行最近的记录，则mh2.*将为NULL，因此mh必须是最新的.

That is, we want mh to be the most recent row in tbl_member_login_history for the given memberid. So we search for another row mh2 that is even more recent. If none more recent than the mh row is found, then mh2.* will be NULL, so mh must be the most recent.

我假设该表的主键列包含递增的值.对于此示例，我假设列名称为pk.

I'm assuming this table has a primary key column that contains increasing values. For this example, I assume the column name is pk.

对登录历史表的引用和都使用LEFT OUTER JOIN，这意味着即使没有匹配的行，也会报告m行.

Using LEFT OUTER JOIN for both references to the login history table means that the m row will be reported even if there is no matching row.

这篇关于MySQL子查询-仅在LEFT JOIN中查找第一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！