MYSQL语句问题——INNER JOIN、LEFT JOIN WITH GROUP BY和MAX

Question

我这辈子都无法使这条语句生效。

SELECT max(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' 

group by pm.p_media_user_id;

我唯一遇到问题的是 max(pm.timestamp)，在分组后我希望它显示 p_media 表中的最新行，但相反它做的恰恰相反并显示最旧的行。因此，我需要 p_media 表中的最新行，这些行按加入 p_users 表的用户 ID 分组。

在此先感谢，如果有人帮助。

Answer 1

正如其他人已经指出的那样，您正在按 p_media_user_id 列进行聚合，然后选择其他非聚合列。这要么根本不会运行，要么会运行但给出不确定的结果。但是，对于每个 p_media_user_id，您似乎只需要 p_media 表中的最新记录。如果是这样，那么这似乎是您打算运行的查询:

SELECT
    pm1.timestamp, pm1.id, pm1.p_media_user_id, pm1.p_media_type, pm1.p_media_file,
    pm1.wall_post, pm1.p_media_location, pm1.p_media_location_name, 
    pm1.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 pm1
INNER JOIN
(
    SELECT p_media_user_id, MAX(timestamp) AS max_timestamp
    FROM p_media
    GROUP BY p_media_user_id
) pm2
    ON pm1.p_media_user_id = pm2.p_media_user_id AND
       pm1.timestamp = pm2.max_timestamp
INNER JOIN p_users AS pu
    ON pm1.p_media_user_id = pu.fb_id 
LEFT JOIN p_alerts AS pa
    ON pm1.id = pa.post_id AND
       pa.p_source_alert_id = '3849084';