使用GROUP BY与MAX()查询数组对应最大num值条目遇无结果问题

嘿，我来帮你搞定这个查询问题，先理清楚核心痛点，再给你落地的方案：

你的需求回顾 #

你需要从数据里捞取：

• item_id在arr_items = [1,2]中的记录
• 每个item_id只保留num值最大的那条，预期得到item_id=1（num=93）和item_id=2（num=40）的条目

但你遇到了两个诡异的情况：

1. 用GROUP BY+MAX(num)组合查询时，直接无结果返回
2. 移除ON子句里的unique_number后查询正常，但搞不懂为啥；而且去掉MAX()能执行，但拿不到对应最大num的条目

正确的查询写法（两种方案） #

假设你的表名叫your_table，包含item_id、num、unique_number这些字段，直接用下面两种方式就能满足需求：

方案一：窗口函数（推荐，逻辑最直观） #

窗口函数是处理这种「分组取最值」场景的最优解，代码可读性拉满：

SELECT item_id, num, unique_number
FROM (
    SELECT 
        item_id, 
        num, 
        unique_number,
        -- 按item_id分组，每组内按num降序排，给每条记录标序号
        ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY num DESC) AS row_rank
    FROM your_table
    WHERE item_id IN (1, 2)
) ranked_data
-- 只取每组的第一条（也就是num最大的那条）
WHERE row_rank = 1;

方案二：关联子查询（兼容老版本数据库） #

如果你的数据库不支持窗口函数（比如某些老版本MySQL），用这种关联子查询也能实现：

SELECT main.item_id, main.num, main.unique_number
FROM your_table main
-- 先找出每个item_id对应的最大num值
JOIN (
    SELECT item_id, MAX(num) AS max_num
    FROM your_table
    WHERE item_id IN (1, 2)
    GROUP BY item_id
) max_num_data 
ON main.item_id = max_num_data.item_id AND main.num = max_num_data.max_num
WHERE main.item_id IN (1, 2);

为什么你的原查询会出问题？ #

1. 为啥加unique_number在ON里就无结果？ #

大概率是你的原查询犯了一个常见错误：在JOIN的ON条件里引用了子查询不存在的字段。

举个你原查询的错误示例：

-- 错误写法：ON里的t2.unique_number根本不存在！
SELECT t1.item_id, MAX(t1.num), t1.unique_number
FROM your_table t1
JOIN (
    SELECT item_id, MAX(num) AS max_num
    FROM your_table
    WHERE item_id IN (1,2)
    GROUP BY item_id
) t2 
ON t1.item_id = t2.item_id AND t1.num = t2.max_num AND t1.unique_number = t2.unique_number
GROUP BY t1.item_id;

子查询t2里只按item_id分组，只返回了item_id和max_num，根本没有unique_number字段！你强行把主表的unique_number和一个不存在的字段关联，JOIN条件永远不满足，自然返回空结果。

而当你移除ON里的unique_number时，JOIN只匹配item_id和num，这时候能找到对应记录，所以查询正常。

2. 为啥去掉MAX()能执行但结果不对？ #

如果你直接写SELECT item_id, num, unique_number FROM your_table WHERE item_id IN (1,2) GROUP BY item_id，这在很多数据库里是不符合SQL标准的（除非数据库开了非标准兼容模式）。即使能执行，返回的num和unique_number也只是随机取的某条记录，大概率不是你要的最大num那条，所以结果不符合预期。

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