MySQL 加入 + "WHERE something=MAX(something)"

Question

我有几个表需要连接。这些表是:

玩家

• 名字
• 姓氏
• 性别
• location_id
• (其他不相关的列)

和

score_entries

• 得分
• 日期时间
• 玩家编号
• (其他不相关的列)

我需要根据 player_id 加入他们，并且只想选择 players.location_id 最大的记录。所以像这样:

SELECT
players.first_name,
players.last_name,
players.gender,
score_entries.score,
score_entries.datetime as jump_date
players.location_id,
FROM score_entries
LEFT JOIN players
ON score_entries.player_id = players.id
WHERE gender="m"
AND location_id="***(ONLY THE HIGHEST LOCATION_ID VALUE IN THE TABLE)***"

Answer 1

请尝试以下查询

SELECT 
    players.first_name, 
    players.last_name, 
    players.gender, 
    score_entries.score, 
    score_entries.datetime as jump_date, 
    players.location_id
FROM score_entries 
LEFT JOIN players 
ON score_entries.player_id = players.id 
WHERE gender="m" 
AND players.location_id = (SELECT MAX(location_id) from players)