MySQL LEFT JOIN带LIMIT仅首行返回关联记录，其余行返回NULL问题

你的问题出在子查询里的LIMIT 1是全局只取一条记录，而不是按每个用户（f_uid）取最新的一条文件记录，所以只有和这条记录f_uid匹配的帖子能拿到f_url，其他用户的帖子自然返回NULL。

下面是几种能实现你预期效果的写法：

方法1：使用窗口函数（MySQL 8.0+、PostgreSQL等支持窗口函数的数据库） #

用ROW_NUMBER()按f_uid分组，给每个用户的文件按fid倒序编号，然后取编号为1的那条（也就是每个用户最新的文件）：

SELECT post.*, user.u_name, files.f_url
FROM TABLE_USER_POST post
INNER JOIN TABLE_USERS user ON user.uid = post.p_uid
LEFT JOIN (
    SELECT 
        f_url, 
        f_uid,
        ROW_NUMBER() OVER(PARTITION BY f_uid ORDER BY fid DESC) AS rn
    FROM TABLE_USER_FILES
    WHERE f_path = "gallery"
) files ON files.f_uid = post.p_uid AND files.rn = 1
ORDER BY post.pid DESC 
LIMIT 0, 20;

方法2：关联子查询（兼容低版本MySQL） #

通过子查询找到每个用户最新的fid，再关联取对应的f_url：

SELECT post.*, user.u_name, files.f_url
FROM TABLE_USER_POST post
INNER JOIN TABLE_USERS user ON user.uid = post.p_uid
LEFT JOIN TABLE_USER_FILES files 
    ON files.f_uid = post.p_uid 
    AND files.f_path = "gallery"
    AND files.fid = (
        SELECT MAX(fid) 
        FROM TABLE_USER_FILES 
        WHERE f_uid = post.p_uid AND f_path = "gallery"
    )
ORDER BY post.pid DESC 
LIMIT 0, 20;

方法3：使用GROUP BY聚合（适用于只需要最新f_url的场景） #

如果fid是按递增生成的，MAX(fid)就是最新的，也可以用GROUP BY分组后取对应f_url：

SELECT post.*, user.u_name, files.f_url
FROM TABLE_USER_POST post
INNER JOIN TABLE_USERS user ON user.uid = post.p_uid
LEFT JOIN (
    SELECT f_uid, SUBSTRING_INDEX(GROUP_CONCAT(f_url ORDER BY fid DESC), ',', 1) AS f_url
    FROM TABLE_USER_FILES
    WHERE f_path = "gallery"
    GROUP BY f_uid
) files ON files.f_uid = post.p_uid
ORDER BY post.pid DESC 
LIMIT 0, 20;

这三种方法都能实现你要的效果：每个用户的所有帖子都关联到该用户最新的那条gallery路径下的文件，没有文件的用户帖子返回NULL。

内容的提问来源于stack exchange，提问作者Peter