MySQL-选择最小值的分组依据 [英] MySQL - Select group by with minimum value

问题描述

我有一个简单的表，其中包含以下数据:

I have a simple table with the following data:

id | descr | val1 | val2
========================
1  | 'aa'  |  1   |  5
2  | 'bb'  |  2   |  7
3  | 'bb'  |  3   |  1
4  | 'bb'  |  4   |  19
5  | 'aa'  |  5   |  1
6  | 'aa'  |  6   |  8
7  | 'bb'  |  7   |  11
8  | 'bb'  |  8   |  3
9  | 'aa'  |  9   |  16
10 | 'aa'  |  10  |  9

我想为每个唯一的descr选择具有最小值val2的行. 预期结果:

I want to select the rows for each unique descr with the minimum value of val2. Expected result:

id | descr | val1 | val2
========================
3  | 'bb'  |  3   |  1
5  | 'aa'  |  5   |  1

我尝试了以下查询:

SELECT *, min(val2) minVal FROM test group by descr

结果与最小值行中的数据无关:

By the result had no relation to the data in the row of the minimum value:

id | descr | val1 | val2 | minVal
==================================
1  | 'aa'  |  1   |  5   |  1
2  | 'bb'  |  2   |  7   |  1

请告知.

谢谢

推荐答案

在MySQL中处理此问题的一种标准方法是不使用广泛的解析函数支持，而是使用子查询来标识最小的val2每个descr组的值.然后，使用此子查询将test的记录限制为要显示在结果集中的内容.

One standard way of dealing with this in MySQL, which doesn't have wide support for analytic functions, is to use a subquery which identifies the minimum val2 values for each descr group. Then use this subquery to restrict the records of test to what you want appearing in your result set.

SELECT t1.*
FROM test t1
INNER JOIN
(
    SELECT descr, MIN(val2) AS min_val2
    FROM test
    GROUP BY descr
) t2
    ON t1.descr = t2.descr AND
       t1.val2  = t2.min_val2

此处演示:

SQLFiddle

这篇关于MySQL-选择最小值的分组依据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！