MySQL子查询ORDER BY与GROUP BY组合失效问题求助

问题原因 #

你的旧查询依赖MySQL的非标准SQL扩展，在新版本MySQL中默认启用了ONLY_FULL_GROUP_BY模式，该模式严格遵循SQL标准：SELECT列表中的列必须是GROUP BY的分组列，或被聚合函数包裹。之前的写法选择了非分组、非聚合的ranking_id和ranking_date，结果完全不可控。此外，子查询中的ORDER BY会被MySQL优化器忽略，无法保证GROUP BY取到最新的行。

解决方案 #

方法1：使用窗口函数（MySQL 8.0+ 推荐） #

利用ROW_NUMBER()窗口函数，按target_page_plain分组后，给每组内的行按日期降序、排名升序编号，取每组的第一行（最新数据）：

SELECT ranking_id, target_page_plain, ranking_date
FROM (
    SELECT 
        ranking_id, 
        target_page_plain, 
        ranking_date,
        -- 按target_page_plain分组，组内按日期降序、排名升序编号
        ROW_NUMBER() OVER (PARTITION BY target_page_plain 
                           ORDER BY ranking_date DESC, ranking_position ASC) AS rn
    FROM keywords_rankings
    WHERE keyword_id = xxx
      AND target_page_plain IS NOT NULL
) a
WHERE rn = 1 -- 取每组的第一行（最新数据）
ORDER BY ranking_date DESC;

方法2：兼容MySQL 5.x版本的写法 #

通过关联子查询先找到每个target_page_plain对应的最新日期，再匹配原表获取完整数据：

SELECT kr.ranking_id, kr.target_page_plain, kr.ranking_date
FROM keywords_rankings kr
INNER JOIN (
    -- 先获取每个target_page_plain的最新日期
    SELECT 
        target_page_plain, 
        MAX(ranking_date) AS max_ranking_date
    FROM keywords_rankings
    WHERE keyword_id = xxx
      AND target_page_plain IS NOT NULL
    GROUP BY target_page_plain
) kr_max ON kr.target_page_plain = kr_max.target_page_plain 
         AND kr.ranking_date = kr_max.max_ranking_date
WHERE kr.keyword_id = xxx
  AND kr.target_page_plain IS NOT NULL
ORDER BY kr.ranking_date DESC;

如果同一URL在最新日期有多个排名记录，需要取排名最靠前的那条，可以调整子查询添加最小排名的过滤：

SELECT kr.ranking_id, kr.target_page_plain, kr.ranking_date
FROM keywords_rankings kr
INNER JOIN (
    SELECT 
        target_page_plain, 
        MAX(ranking_date) AS max_ranking_date,
        MIN(ranking_position) AS min_ranking_pos
    FROM keywords_rankings
    WHERE keyword_id = xxx
      AND target_page_plain IS NOT NULL
    GROUP BY target_page_plain
) kr_max ON kr.target_page_plain = kr_max.target_page_plain 
         AND kr.ranking_date = kr_max.max_ranking_date
         AND kr.ranking_position = kr_max.min_ranking_pos
WHERE kr.keyword_id = xxx
  AND kr.target_page_plain IS NOT NULL
ORDER BY kr.ranking_date DESC;

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