如何按一列分组并在 T/SQL 中检索具有另一列最小值的行? [英] How do you group by one column and retrieve a row with the minimum value of another column in T/SQL?

问题描述

所以我知道这是一个非常愚蠢的问题，但是(正如相当冗长的标题所说)我想知道如何执行以下操作:

So I know this is a pretty dumb question, however (as the rather lengthily title says) I would like to know how do the following:

我有一张这样的桌子:

ID Foo Bar Blagh
----------------
1  10  20  30
2  10  5   1
3  20  50  40
4  20  75  12

我想按 Foo 分组，然后拉出具有最小 Bar 的行，即我想要以下内容:

I want to group by Foo, then pull out rows with minimum Bar, i.e. I want the following:

ID Foo Bar Blagh
----------------
2  10  5   1
3  20  50  40

我一辈子都无法找出正确的 SQL 来检索它.我想要类似的东西:

I can't for the life of me work out the correct SQL to retrieve this. I want something like:

SELECT ID, Foo, Bar, Blagh
FROM Table
GROUP BY Foo
HAVING(MIN(Bar))

然而，这显然不起作用，因为这是完全无效的 HAVING 语法，并且 ID、Foo、Bar 和 Blagh 没有聚合.

However this clearly doesn't work as that is completely invalid HAVING syntax and ID, Foo, Bar and Blagh are not aggregated.

我做错了什么?

推荐答案

这个几乎是同一个问题，但它有一些答案！

This is almost exactly the same question, but it has some answers!

这是我在模拟你的桌子:

Here's me mocking up your table:

declare @Borg table (
    ID int,
    Foo int,
    Bar int,
    Blagh int
)
insert into @Borg values (1,10,20,30)
insert into @Borg values (2,10,5,1)
insert into @Borg values (3,20,50,70)
insert into @Borg values (4,20,75,12)

然后你可以做一个匿名内连接来获取你想要的数据.

Then you can do an anonymous inner join to get the data you want.

select B.* from @Borg B inner join 
(
    select Foo,
        MIN(Bar) MinBar 
    from @Borg 
    group by Foo
) FO
on FO.Foo = B.Foo and FO.MinBar = B.Bar

编辑 Adam Robinson 很有帮助地指出当 Bar 的最小值重复时，这个解决方案有可能返回多行，并消除了 bar 为 null 的任何 foo 值"

EDIT Adam Robinson has helpfully pointed out that "this solution has the potential to return multiple rows when the minimum value of Bar is duplicated, and eliminates any value of foo where bar is null"

根据您的用例，重复 Bar 的重复值可能是有效的 - 如果您想在 Borg 中找到 Bar 最小的所有值，那么同时获得两个结果似乎是可行的方法.

Depending upon your usecase, duplicate values where Bar is duplicated might be valid - if you wanted to find all values in Borg where Bar was minimal, then having both results seems the way to go.

如果您需要在聚合的字段中捕获 NULLs(在本例中为 MIN)，那么您可以coalesce NULL 与可接受的高 (或低)价值(这是一个黑客):

If you need to capture NULLs in the field across which you are aggregating (by MIN in this case), then you could coalesce the NULL with an acceptably high (or low) value (this is a hack):

...
MIN(coalesce(Bar,1000000)) MinBar -- A suitably high value if you want to exclude this row, make it suitably low to include
...

或者您可以使用 UNION 并将所有此类值附加到结果集的底部.

Or you could go for a UNION and attach all such values to the bottom of your resultset.

on FO.Foo = B.Foo and FO.MinBar = B.Bar
union select * from @Borg where Bar is NULL

后者不会将@Borg 中的值与相同的 Foo 值分组，因为它不知道如何在它们之间进行选择.

The latter will not group values in @Borg with the same Foo value because it doesn't know how to select between them.

这篇关于如何按一列分组并在 T/SQL 中检索具有另一列最小值的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！