MS Access SQL:汇总最小值，但检索其他字段 [英] MS Access SQL: Aggregating on Min Value but retrieving other fields

问题描述

这可能是一个非常简单的问题，但是我无法理解如何在MS Access中解决它.可能以前已经回答过，但是我找不到它.

This may be an extremely easy question but I can't get my head around how to solve it in MS Access. It has probably been answered before but I haven't been able to find it.

我有一个包含3列的表:col1是对象ID，col2和col3是度量.我建立了一个查询，以便为每个对象在col2中获得相应的最小值.通过简单的分组查询，这可以正常工作.问题是当我尝试拉col3(与找到最小值的行相关联)时，它将不再正确分组.

I have a table with 3 columns: col1 is an object ID, col2 and col3 are measurements. I've built a query so that for each object we get the corresponding minimum value in col2. This works OK with a simple group-by query. The problem is when I try to pull col3 (associated with the row where minimum value was found), it will no longer group-by properly.

我一直在使用group-by子句，试图将其分为几个查询，但到目前为止还算不上运气.

I've been playing with the group-by clause and tried to divide into several queries but so far no luck.

这是我(左)想得到(右)的例子:

Here's an example of what I have (left) and would like to get (right):

col1  col2  col3   --->   col1  minC2 col3 
----  ----  ----          ----  ----  ----
1     0     1.8           1     0     1.8   
1     1     1.4           2     2     2.5
2     4     1.1           3     1     7.6
2     6     4.7
2     2     2.5
3     4     3.3
3     1     7.6

我希望这是有道理的.任何帮助/MS Access SQL语法表示赞赏... 谢谢！

I hope this makes sense. Any help/MS Access SQL syntax is appreciated... Thanks!

推荐答案

假设您想要获得的第二行是[2 2 2.5]，这就是您要查找的内容:

Assuming that the second line of what you like to get is [2 2 2.5], this is what you're looking for:

select a.col1, a.colm, m.col3
from
    (
        select col1, min(col2) as colm
        from test
        group by col1
    ) as a
inner join test m on a.col1 = m.col1 and a.colm = m.col2

这篇关于MS Access SQL:汇总最小值，但检索其他字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！