如何按一列分组并在 T/SQL 中检索具有另一列最小值的行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1212093/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do you group by one column and retrieve a row with the minimum value of another column in T/SQL?
提问by ljs
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:
所以我知道这是一个非常愚蠢的问题，但是（正如相当冗长的标题所说）我想知道如何执行以下操作：
I have a table like this:
我有一张这样的表：
ID Foo Bar Blagh
----------------
1 10 20 30
2 10 5 1
3 20 50 40
4 20 75 12
I want to group by Foo, then pull out rows with minimum Bar, i.e. I want the following:
我想按 Foo 分组，然后用最小 Bar 拉出行，即我想要以下内容：
ID Foo Bar Blagh
----------------
2 10 5 1
3 20 50 40
I can't for the life of me work out the correct SQL to retrieve this. I want something like:
我一生都无法找出正确的 SQL 来检索它。我想要这样的东西：
SELECT ID, Foo, Bar, Blagh
FROM Table
GROUP BY Foo
HAVING(MIN(Bar))
However this clearly doesn't work as that is completely invalid HAVING syntax and ID, Foo, Bar and Blagh are not aggregated.
然而，这显然不起作用，因为这是完全无效的 HAVING 语法，并且 ID、Foo、Bar 和 Blagh 没有聚合。
What am I doing wrong?
我究竟做错了什么？
采纳答案by butterchicken
Thisis 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
EDITAdam 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"
编辑Adam Robinson 很有帮助地指出，“当 Bar 的最小值重复时，此解决方案有可能返回多行，并消除 bar 所在的任何 foo 值null”
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.
根据您的用例，重复 Bar 的重复值可能是有效的 - 如果您想在 Borg 中查找 Bar 最小的所有值，那么同时拥有两个结果似乎是可行的方法。
If you need to capture NULLsin the field across which you are aggregating (by MIN in this case), then you could coalescethe NULL with an acceptably high (or low) value (this is a hack):
如果您需要NULLs在聚合的字段中进行捕获（在这种情况下为 MIN），那么您可以coalesce使用可接受的高（或低）值（这是一个黑客）：
...
MIN(coalesce(Bar,1000000)) MinBar -- A suitably high value if you want to exclude this row, make it suitably low to include
...
Or you could go for a UNION and attach all such values to the bottom of your resultset.
或者您可以选择 UNION 并将所有此类值附加到结果集的底部。
on FO.Foo = B.Foo and FO.MinBar = B.Bar
union select * from @Borg where Bar is NULL
The latter will not group values in @Borg with the same Foovalue because it doesn't know how to select between them.
后者不会将@Borg 中的值分组为相同的Foo值，因为它不知道如何在它们之间进行选择。
回答by Adam Robinson
select
ID,
Foo,
Bar,
Blagh
from Table
join (
select
ID,
(row_number() over (order by foo, bar) - rank() over (order by foo)) as rowNumber
) t on t.ID = Table.ID and t.rowNumber = 0
This joins on the table again, but this time adds a relative row number for the value for bar, as if it were sorted ascending within each value of foo. By filtering on rowNumber = 0, it selects only the lowest values for barfor each value of foo. This also effectively eliminates the group byclause, since you're now only retrieving one row per foo.
这再次连接到表上，但这次为 的值添加了一个相对行号bar，就好像它在 的每个值内升序排序foo。通过筛选rowNumber = 0，它只为bar的每个值选择最低值foo。这也有效地消除了该group by子句，因为您现在每个foo.
回答by HAdes
Another option would be something along the lines of the following:
另一种选择是类似于以下内容：
DECLARE @TEST TABLE( ID int, Foo int, Bar int, Blagh int)
INSERT INTO @TEST VALUES (1,10,20,30)
INSERT INTO @TEST VALUES (2,10,5,1)
INSERT INTO @TEST VALUES (3,20,50,70)
INSERT INTO @TEST VALUES (4,20,75,12)
SELECT Id, Foo, Bar, Blagh
FROM (
SELECT id, Foo, Bar, Blagh, CASE WHEN (Min(Bar) OVER(PARTITION BY FOO) = Bar) THEN 1 ELSE 0 END as MyRow
FROM @TEST) t
WHERE MyRow = 1
Although this still requires a sub-query it does eliminate the need for joins.
虽然这仍然需要一个子查询，但它确实消除了对连接的需要。
Just another option.
只是另一种选择。
回答by Blorgbeard is out
My understanding is that you can't really do this in one go.
我的理解是，你不能一次真正做到这一点。
select Foo, min(Bar) from table group by Foo
,, gets you the minimum Bar for each distinct Foo. But you can't tie that minimum to a particular ID, because there could be more than one row with that Bar value.
,, 为您提供每个不同 Foo 的最小 Bar。但是您不能将该最小值与特定 ID 联系起来，因为该 Bar 值可能不止一行。
What you can do is something like this:
你可以做的是这样的：
select * from Table t
join (
select Foo, min(Bar) as minbar
from Table group by Foo
) tt on t.Foo=tt.Foo and t.Bar=tt.minbar
Note that if there ismore than one row with the minimum Bar value, you'll get them all with the above query.
请注意，如果是多行与最低值吧，你会得到他们所有上面的查询。
Now, I am not claiming to be a SQL guru, and it is late where I am, and I may be missing something, but there's my $0.02 :)
现在，我并没有声称自己是 SQL 大师，而且我现在已经很晚了，我可能会遗漏一些东西，但这是我的 0.02 美元 :)
回答by Adriaan Stander
This might help:
这可能有帮助：
DECLARE @Table TABLE(
ID INT,
Foo FLOAT,
Bar FLOAT,
Blah FLOAT
)
INSERT INTO @Table (ID,Foo,Bar,Blah) SELECT 1, 10 ,20 ,30
INSERT INTO @Table (ID,Foo,Bar,Blah) SELECT 2, 10 ,5 ,1
INSERT INTO @Table (ID,Foo,Bar,Blah) SELECT 3, 20 ,50 ,40
INSERT INTO @Table (ID,Foo,Bar,Blah) SELECT 4, 20 ,75 ,12
SELECT t.*
FROM @Table t INNER JOIN
(
SELECT Foo,
MIN(Bar) MINBar
FROM @Table
GROUP BY Foo
) Mins ON t.Foo = Mins.Foo
AND t.Bar = Mins.MINBar
回答by user3274352
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)
select * from @Borg
select Foo,Bar,Blagh from @Borg b
where Bar = (select MIN(Bar) from @Borg c where c.Foo = b.Foo)
