在MySQL中为每个项目选择最小值 [英] Select min value per item in MySQL

问题描述

您好，我有以下MySQL表:

Hello I have the following MySQL table:

id       item         value
1         A             11
2         A             20
3         B              2
4         C              1
5         B             14
6         C             12

我想做的是选择具有最低值的项目-根据此论坛的搜索，我试图做的是使用以下查询:

What I'm trying to do is to select items with the lowest values - what I tried to do based on search of this forum is to use the following query:

  SELECT *,
         MIN(value) 
    FROM mytable 
GROUP BY item

预期结果应为 A => 11，B => 2; C => 1 ，但是对于某些项目，最小值是正确的，而对于其他某些项目，最小值是不同的值.请注意，mytable大约包含100行.

The expected result should be A => 11, B => 2; C => 1 however for some of the items the min value is correct and for some other items the min value is different value. Please note that mytable consist of approx ~100 rows.

推荐答案

您可以尝试这种方式.

SELECT t.* FROM mytable  t
JOIN 
( SELECT item, MIN(value) minVal
  FROM mytable  GROUP BY item
) t2
ON t.value = t2.minVal AND t.item = t2.item;

这篇关于在MySQL中为每个项目选择最小值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！