PHP MySQL四表联查：如何仅获取table_4最新单条关联数据

我在进行4张表的PHP MySQL联查时遇到问题：目前可以实现多表关联，但联查后返回多条重复结果，需要仅获取table_4中每个uid对应的最新条目——table_4存在多条同uid的记录，但只需要最新的那一条。

初始查询代码 #

$display = $db->select("
    SELECT tb1.*, tb2.*, tb3.*, tb4.* 
    FROM table_1 AS tb1 
    LEFT JOIN table_2 AS tb2 ON tb2.uid = tb1.uid 
    LEFT JOIN table_3 AS tb3 ON tb3.ply = tb2.pcn 
    LEFT JOIN table_4 AS tb4 ON tb4.uid = tb2.uid 
    WHERE tb3.flt = " . $postId . " 
");

示例中Test 2应仅出现一次，对应1.60、16.00、1.60的记录，而非0.00、29.90、2.90的旧记录。

尝试的修改代码 #

$display = $db->select("
    SELECT tb1.*, tb2.*, tb3.*, tb4.* 
    FROM table_1 AS tb1 
    LEFT JOIN table_2 AS tb2 ON tb2.uid = tb1.uid 
    LEFT JOIN table_3 AS tb3 ON tb3.ply = tb2.pcn 
    INNER JOIN (
        SELECT tb4.* 
        FROM table_4 AS tb4 
        GROUP BY tb4.uid 
    ) AS tb4 ON tb4.uid = tb3.uid 
    WHERE tb3.flt = " . $postId . " 
");

解决方案 #

直接对table_4按uid分组时，MySQL不会自动返回最新记录，而是随机选取一条。要获取每个uid对应的最新条目，需先通过子查询定位每个uid的最新记录标识（如自增ID或创建时间），再关联回table_4获取完整数据。

正确查询代码（按创建时间判断最新） #

$display = $db->select("
    SELECT tb1.*, tb2.*, tb3.*, tb4.* 
    FROM table_1 AS tb1 
    LEFT JOIN table_2 AS tb2 ON tb2.uid = tb1.uid 
    LEFT JOIN table_3 AS tb3 ON tb3.ply = tb2.pcn 
    LEFT JOIN (
        -- 子查询筛选每个uid的最新记录（假设用create_time字段判断）
        SELECT * FROM table_4 AS t4 
        WHERE (t4.uid, t4.create_time) IN (
            SELECT uid, MAX(create_time) 
            FROM table_4 
            GROUP BY uid
        )
    ) AS tb4 ON tb4.uid = tb2.uid 
    WHERE tb3.flt = " . $postId . " 
");

优化方案（按自增ID判断最新） #

如果table_4有自增主键（如id），用MAX(id)判断最新比时间戳更可靠，修改子查询即可：

SELECT * FROM table_4 AS t4 
WHERE (t4.uid, t4.id) IN (
    SELECT uid, MAX(id) 
    FROM table_4 
    GROUP BY uid
)

注意事项 #

• 原修改版用了INNER JOIN，会过滤掉table_4中无对应记录的条目；若需保留原LEFT JOIN逻辑（即使table_4无记录也显示），需改回LEFT JOIN。

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