mysql - SQL查询以获取多个最小值

Question

输入

+----+----+----+
| in | tn | 20 |
+----+----+----+
| in | ka | 18 |
+----+----+----+
| in | ap | 30 |
+----+----+----+
| us | la | 12 |
+----+----+----+
| us | ca | 20 |
+----+----+----+
| us | ny | 5  |
+----+----+----+

期望的输出

+----+----+----+
| in | ka | 18 |
+----+----+----+
| us | ny | 5  |
+----+----+----+

在上面的输出中，我需要每个国家及其具有最小值的州。

select a.country, a.state, a.value
from table1 a
where a.value in (select min(value) from table1);

通过上面的查询，我得到了基于整个表的最小值的输出。

+----+----+---+
| us | ny | 5 |
+----+----+---+

我需要每个国家及其各自状态下的最小值。

Answer 1

使用您的方法，您需要一个相关的子查询:

select a.country, a.state, a.value
from table1 a
where a.value in (select min(value)
                  from table1 b
                  where a.country = b.country);