MySQL查询各关联ID对应最小值及对应列数据的问题

问题分析 #

你遇到的是GROUP BY聚合时的典型问题：当使用MIN()这类聚合函数时，非聚合列（比如category）不会自动关联到聚合结果对应的那一行，数据库通常会返回分组内任意一行的category值，这就是你看到结果不匹配的原因。

先明确你的表结构：

tab1

tab2

解决方案 #

这里提供两种可靠的实现方式，适配不同数据库场景：

方案1：窗口函数（推荐，支持现代SQL数据库）

用ROW_NUMBER()窗口函数给每个reference_id分组内的行按value排序，最小value的行序号为1，最后筛选出这一行即可：

SELECT t.name, t.id, t.value AS VAL, t.category
FROM (
    SELECT 
        tab1.name,
        tab1.id,
        tab2.value,
        tab2.category,
        -- 按tab1.id分组，组内按value升序排序，最小value的行rn=1
        ROW_NUMBER() OVER (PARTITION BY tab1.id ORDER BY tab2.value ASC) AS rn
    FROM tab1
    JOIN tab2 ON tab1.id = tab2.reference_id
) t
WHERE t.rn = 1;

如果同一个reference_id下存在多个相同的最小value，你可以把ROW_NUMBER()换成RANK()或DENSE_RANK()，这样会返回所有符合最小value条件的行。

方案2：子查询关联（兼容旧版数据库）

先算出每个reference_id对应的最小value，再关联回原表匹配对应的category：

SELECT 
    tab1.name,
    tab1.id,
    min_vals.min_value AS VAL,
    tab2.category
FROM tab1
-- 先获取每个reference_id的最小value
JOIN (
    SELECT reference_id, MIN(value) AS min_value
    FROM tab2
    GROUP BY reference_id
) min_vals ON tab1.id = min_vals.reference_id
-- 关联回tab2，匹配reference_id和最小value，拿到对应的category
JOIN tab2 ON min_vals.reference_id = tab2.reference_id 
    AND min_vals.min_value = tab2.value;

这种方式同样会返回所有符合最小value条件的行，如果有多个相同最小值的话。

为什么你的原始SQL失效？ #

在严格SQL模式（比如MySQL的ONLY_FULL_GROUP_BY开启时），你的原始SQL会直接报错——因为category既不在GROUP BY子句中，也不是聚合函数。如果没报错，说明数据库处于宽松模式，此时category是分组内随机取的一行值，自然无法对应到最小值的那一行。

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