MySQL：GROUP BY无视前置ORDER BY，如何获取分组目标ID？

问题根源 #

你原来的写法里，campaign_items的ORDER BY不会影响后续GROUP BY的结果——因为在没有LIMIT的情况下，CTE的排序只是逻辑上的，数据库执行计划可能会忽略这个排序；而GROUP BY时如果SELECT非聚合列，数据库会返回分组内的任意一行，不是你想要的排序后的第一行。

正确实现方案 #

用窗口函数ROW_NUMBER()可以精准获取每个campaign_main_ref_id分组内item_state_c最大的行，同时筛选出存在重复的分组：

WITH
frontload_array AS
(
  SELECT jt.ID 
  FROM JSON_TABLE('[182723,182722,182721,178216,2]', '$[*]' COLUMNS(ID int PATH '$')) AS jt
),
campaign_items_ranked AS
(
    SELECT 
        ci.ID, 
        ci.campaign_main_ref_id, 
        ci.item_state_c,
        -- 按campaign分组，item_state_c降序排，每组最大的行号为1
        ROW_NUMBER() OVER (PARTITION BY ci.campaign_main_ref_id ORDER BY ci.item_state_c DESC) AS rn,
        -- 计算每个campaign的行数，用于判断是否重复
        COUNT(*) OVER (PARTITION BY ci.campaign_main_ref_id) AS cnt
    FROM campaign_item ci
    WHERE ci.item_object_type_sc = 4
    AND ci.item_object_subtype_sc = 1
    AND ci.item_object_ref_id IN (SELECT ID FROM frontload_array)
)
SELECT ID, campaign_main_ref_id, item_state_c
FROM campaign_items_ranked
WHERE rn = 1 AND cnt > 1;

代码说明 #

• ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)：给每个campaign_main_ref_id分组内的行按item_state_c降序编号，最大的那行编号为1。
• COUNT(*) OVER (PARTITION BY ...)：计算每个分组的总行数，筛选cnt >1就能只保留存在重复的campaign。
• 最后通过rn=1拿到每个重复分组里item_state_c最大的对应ID。

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