MySQL内部连接最大 [英] mysql inner join max

问题描述

我需要您的内部联接和最大帮助.我已经研究了其他问题，但无法解决...

I need your help with a inner join and max. I already researched other questions but I could not solve...

我有三个表:

• 成员(member_id，姓名)
• 位置(location_id，名称，顺序)
• member_location(ID，member_id，location_id)

我只需要从member_location中选择按member_location.memberId具有最高顺序组的记录即可.

I need select just the record from member_location with the highest order group by member_location.memberId.

示例:

Member
1, Jack Sparrow

Location
1, Mexico, 2
2, Punta Cana, 3
3, Cuba, 1

member_location
1, 1, 3
1, 1, 2
1, 1, 1

在member_location上，我有3条记录用于同一成员，对于我的查询，我需要获取member_location的第二行(1、1、2)，因为位置2的顺序最大.

On member_location I have 3 records for the same member, with my query I need get the second row of member_location (1, 1, 2) because the order of location 2 is the greatest .

我尝试:

select ml.memberId, ml.locationId, max(l.order)
from member_location ml inner join
     location l
     on ml.locationId=l.id
group by ml.memberId;

结果:1​​,1,3-顺序还可以，但是locationId没有.

result: 1,1,3 -The order it's ok but the locationId no.

我也尝试:

select ml.locationId, ml.memberId
from member_location ml inner join
     (select id, max(order) from location) l
     on ml.locationId = l.id
group by ml.memberId;

在回复中，我收到了第一个位置的记录.

On the response I receive the record with the first location.

推荐答案

我认为这可能是您想要的:

I think maybe this is what you want:

select ml.memberId, ml.locationid
from member_location ml 
inner join location l on ml.locationId = l.id
where l.order = (
    select max(order) max_order 
    from location
    join member_location on location.id = member_location.locationid
    where member_location.memberid = ml.memberid
)

这将获得对任何用户而言顺序最高的位置；不限于一个相关子查询可确保将max(order)仅应用于与用户相关的位置集合(没有相关性，max(order)可能是特定用户member_locations不包括的位置).

This would get the location with the highest order for any user; not limited to one. The correlated subquery makes sure that the max(order) is applied only to the set of locations relevant for the user (without the correlation the max(order) could be a location that the specific users member_locations doesn't include).

如果只需要特定用户的数据，则可以在查询末尾添加where ml.memberId = ?.

If you only want data for a specific user you can add a where ml.memberId = ? at the end of the query.

这篇关于MySQL内部连接最大的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！