MySQL查询异常：INNER JOIN、LEFT JOIN结合GROUP BY与MAX不符合预期

我来帮你搞定这个SQL问题！你遇到的核心问题是：当你用GROUP BY pm.p_media_user_id时，虽然对pm.timestamp用了MAX()，但其他未聚合的列（比如pm.id、pm.p_media_file等）会被数据库返回分组内的任意一行数据，而不是和MAX(timestamp)对应的那一行——这就是为什么你拿到的是最旧的行，而不是最新的。

下面给你两种可靠的解决方案，根据你的数据库版本选择：

方案1：用窗口函数（推荐，适合MySQL 8.0+、PostgreSQL、SQL Server等） #

窗口函数ROW_NUMBER()可以帮我们给每个用户的行按时间排序，直接取最新的那一行：

WITH ranked_media AS (
    SELECT 
        pm.*,
        pa.p_source_alert_id, 
        pa.post_id, 
        pa.p_target_alert_id,
        -- 按用户分组，时间倒序排序，最新行标记为1
        ROW_NUMBER() OVER (PARTITION BY pm.p_media_user_id ORDER BY pm.timestamp DESC) AS rn
    FROM p_media AS pm
    LEFT JOIN p_alerts AS pa ON pm.id = pa.post_id AND pa.p_source_alert_id = '3849084'
)
SELECT 
    rm.timestamp, 
    rm.id, 
    rm.p_media_user_id, 
    rm.p_media_type, 
    rm.p_media_file, 
    rm.wall_post, 
    rm.p_media_location,
    rm.p_media_location_name, 
    rm.p_media_category, 
    rm.p_source_alert_id, 
    rm.post_id, 
    rm.p_target_alert_id, 
    pu.fb_id, 
    pu.username, 
    pu.city, 
    pu.sex, 
    pu.main_image 
FROM ranked_media AS rm
INNER JOIN p_users AS pu ON rm.p_media_user_id = pu.fb_id
WHERE rm.rn = 1; -- 只取每个用户的最新行

如果同一个用户有多条同一时间的最新行，想要全部保留的话，把ROW_NUMBER()换成RANK()即可。

方案2：子查询关联（适合旧版本MySQL等不支持窗口函数的数据库） #

先通过子查询拿到每个用户的最新时间戳，再关联原表筛选出对应行：

SELECT 
    pm.timestamp, 
    pm.id, 
    pm.p_media_user_id, 
    pm.p_media_type, 
    pm.p_media_file, 
    pm.wall_post, 
    pm.p_media_location,
    pm.p_media_location_name, 
    pm.p_media_category, 
    pa.p_source_alert_id, 
    pa.post_id, 
    pa.p_target_alert_id, 
    pu.fb_id, 
    pu.username, 
    pu.city, 
    pu.sex, 
    pu.main_image 
FROM p_media AS pm
INNER JOIN p_users AS pu ON pm.p_media_user_id = pu.fb_id
LEFT JOIN p_alerts AS pa ON pm.id = pa.post_id AND pa.p_source_alert_id = '3849084'
-- 关联子查询，筛选出每个用户的最新行
INNER JOIN (
    SELECT p_media_user_id, MAX(timestamp) AS latest_timestamp
    FROM p_media
    GROUP BY p_media_user_id
) AS latest ON pm.p_media_user_id = latest.p_media_user_id 
           AND pm.timestamp = latest.latest_timestamp;

这个方法会返回所有时间戳等于用户最新时间的行，如果有同时间的多条数据，都会被保留。

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