MySQL关联单表最新行：PHP项目展示餐厅及最新评论求助

1. 单条SQL查询语句 #

使用窗口函数ROW_NUMBER()可以确保每个餐厅只返回一条最新评论记录，彻底避免左连接导致的重复行问题：

SELECT 
    r.id AS restaurant_id,
    r.name,
    r.cuisine,
    r.address,
    r.image_file,
    rev.id AS review_id,
    rev.header,
    rev.content,
    rev.posted_at,
    rev.modified_at,
    u.username AS reviewer_username
FROM restaurants r
LEFT JOIN (
    SELECT 
        *,
        -- 按餐厅分组，先按发布时间倒序，再按修改时间倒序，取每组第一条
        ROW_NUMBER() OVER (PARTITION BY restaurant_id ORDER BY posted_at DESC, modified_at DESC) AS rn
    FROM reviews
) rev ON r.id = rev.restaurant_id AND rev.rn = 1
LEFT JOIN users u ON rev.user_id = u.id;

如果你的数据库不支持窗口函数（如旧版MySQL），可以用子查询关联最大时间的替代方案：

SELECT 
    r.id AS restaurant_id,
    r.name,
    r.cuisine,
    r.address,
    r.image_file,
    rev.id AS review_id,
    rev.header,
    rev.content,
    rev.posted_at,
    rev.modified_at,
    u.username AS reviewer_username
FROM restaurants r
LEFT JOIN (
    SELECT restaurant_id, MAX(posted_at) AS latest_post_time
    FROM reviews
    GROUP BY restaurant_id
) latest_rev ON r.id = latest_rev.restaurant_id
LEFT JOIN reviews rev ON rev.restaurant_id = latest_rev.restaurant_id AND rev.posted_at = latest_rev.latest_post_time
LEFT JOIN users u ON rev.user_id = u.id;

注：第二种方案如果遇到同一餐厅同一时间有多条评论，可能返回重复行，优先推荐窗口函数方案。

2. PHP单个foreach循环实现展示 #

查询结果集中每一行对应一个餐厅（含最新评论或空值），直接循环即可完成展示：

// 假设$pdo是你的数据库连接实例
$stmt = $pdo->query($sql);
$restaurants = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($restaurants as $restaurant) {
    // 输出餐厅基础信息
    echo "<div class='restaurant-card'>";
    echo "<h2>{$restaurant['name']}</h2>";
    echo "<p>菜系：{$restaurant['cuisine']}</p>";
    echo "<p>地址：{$restaurant['address']}</p>";
    if (!empty($restaurant['image_file'])) {
        echo "<img src='{$restaurant['image_file']}' alt='{$restaurant['name']} 门店图'>";
    }

    // 输出最新评论（存在则显示）
    if (!empty($restaurant['review_id'])) {
        echo "<div class='latest-review'>";
        echo "<h3>最新评论：{$restaurant['header']}</h3>";
        echo "<p>{$restaurant['content']}</p>";
        echo "<p>评论人：{$restaurant['reviewer_username']} | 发布时间：{$restaurant['posted_at']}</p>";
        if ($restaurant['modified_at'] !== $restaurant['posted_at']) {
            echo "<p>最后修改：{$restaurant['modified_at']}</p>";
        }
        echo "</div>";
    }
    echo "</div>";
}

这样就通过单条SQL和单个foreach循环完成了需求，每个餐厅只会被处理一次，不会出现重复行问题。

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