mysql left join第一个_MySQL子查询-仅在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

So that returns what's expected.

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

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.

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.

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

解决方案

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;

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.

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

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.