MySQL - 获取最低值

2021-12-26 00:00:00 group-by mysql

我的数据库结构包含列:id、name、value、dealer.我想为 每个 dealer 检索具有最低 value 的行.我一直在尝试使用 MIN() 和 GROUP BY，但仍然没有解决方案.

My database structure contains columns: id, name, value, dealer. I want to retrieve row with lowest value for each dealer. I've been trying to mess up with MIN() and GROUP BY, still - no solution.

推荐答案

Solution1:

SELECT t1.* FROM your_table t1
JOIN (
  SELECT MIN(value) AS min_value, dealer
  FROM your_table 
  GROUP BY dealer
) AS t2 ON t1.dealer = t2.dealer AND t1.value = t2.min_value

解决方案 2:

SELECT t1.* FROM your_table t1
LEFT JOIN your_table t2
ON t1.dealer = t2.dealer AND t1.value > t2.value
WHERE t2.value IS NULL

这个问题非常有名，所以在Mysql的手册中有专门的页面.

This problem is very famous, so there is a special page for this in Mysql's manual.

检查:行保持某列的分组最大值/最小值

相关文章