mysql - 如何仅在 mysql 中获取最小组行？

Question

示例 1:

表test1的一部分:

name|mygroup| scale
----------------
ben | 1   | 1
tom | 1   | 2
jim | 2   | 1
tim | 2   | 2
..

输出应该是:

name|mygroup| scale
----------------
ben | 1   | 1
jim | 2   | 1

我尝试:

select name,mygroup,min(scale) from test1 group by mygroup;

但它没有给出正确的输出。

谢谢

Answer 1

SELECT t.name, t.mygroup, t.scale
    FROM test1 t
        INNER JOIN (SELECT mygroup, MIN(scale) AS MinScale
                        FROM test1
                        GROUP BY mygroup) q
            ON t.mygroup = q.mygroup
                AND t.scale = q.MinScale