如何在 SQL 查询中选择每组的第一行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1881728/
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 I select the first row per group in an SQL Query?
提问by mafu
I've got this SQL query:
我有这个 SQL 查询：
SELECT Foo, Bar, SUM(Values) AS Sum
FROM SomeTable
GROUP BY Foo, Bar
ORDER BY Foo DESC, Sum DESC
This results in an output similar to this:
这会产生与此类似的输出：
47 1 100
47 0 10
47 2 10
46 0 100
46 1 10
46 2 10
44 0 2
I'd like to have only the first row per Foo and ignore the rest.
我想每个 Foo 只有第一行，而忽略其余部分。
47 1 100
46 0 100
44 0 2
How do I do that?
我怎么做？
回答by Anton Georgiev
declare @sometable table ( foo int, bar int, value int )
insert into @sometable values (47, 1, 100)
insert into @sometable values (47, 0, 10)
insert into @sometable values (47, 2, 10)
insert into @sometable values (46, 0, 100)
insert into @sometable values (46, 1, 10)
insert into @sometable values (46, 2, 10)
insert into @sometable values (44, 0, 2)
;WITH cte AS
(
SELECT Foo, Bar, SUM(value) AS SumValue, ROW_NUMBER() OVER(PARTITION BY Foo ORDER BY FOO DESC, SUM(value) DESC) AS RowNumber
FROM @SomeTable
GROUP BY Foo, Bar
)
SELECT *
FROM cte
WHERE RowNumber = 1
回答by tmeisenh
I might disagree with rjmunru in that using Ansii style joins can often be easier to read than subqueries but to each his own -- I just follow what our DBAs say to do.
我可能不同意 rjmunru 的观点，因为使用 Ansii 样式的连接通常比子查询更容易阅读，但对于每个人来说，我只是按照我们的 DBA 所说的去做。
If you just want the first result from a query, you might be able to use a rownum (if using oracle, other databases probably have something similiar).
如果您只想要查询的第一个结果，您可以使用 rownum（如果使用 oracle，其他数据库可能有类似的东西）。
select * from foo_t f where f.bar = 'bleh' and rownum = 1
select * from foo_t f where f.bar = 'bleh' and rownum = 1
Of course a HAVING clause might also be appropriate, depending on what you are trying to do.
当然，HAVING 子句也可能是合适的，这取决于您要做什么。
"HAVING is used to perform an action on groups created by GROUP BY similar to that of the WHERE clause on rows in a basic SQL statement. The WHERE clause limits the rows evaluated. The HAVING clause limits the grouped rows returned."
“HAVING 用于对 GROUP BY 创建的组执行操作，类似于基本 SQL 语句中的 WHERE 子句对行的操作。WHERE 子句限制评估的行。HAVING 子句限制返回的分组行。”
hth
第
回答by Gerauld
It's an old post but I had the same problem today. I've solved it by trying many queries until it works. I'm using SQL Compact 3.5 with visual basic 2010.
这是一个旧帖子，但我今天遇到了同样的问题。我已经通过尝试许多查询来解决它，直到它起作用为止。我在 Visual Basic 2010 中使用 SQL Compact 3.5。
This example is for a table named "TESTMAX" with columns "Id" (primary key), "nom" (name) and "value", you can use this to obtain rows with max "value" for each "nom" :
此示例适用于名为“TESTMAX”的表，其中包含“Id”（主键）、“nom”（名称）和“value”列，您可以使用它来获取每个“nom”具有最大“value”的行：
SELECT TESTMAX.Id, TESTMAX.NOM, TESTMAX.Value
FROM TESTMAX INNER JOIN
TESTMAX AS TESTMAX_1 ON TESTMAX.NOM = TESTMAX_1.NOM
WHERE (TESTMAX.Value IN
(SELECT MAX(Value) AS Expr1
FROM TESTMAX AS TESTMAX_2
WHERE (NOM = TESTMAX_1.NOM)))
GROUP BY TESTMAX.Id, TESTMAX.NOM, TESTMAX.Value
If you want to delete the other rows, you can also use :
如果要删除其他行，还可以使用：
DELETE FROM TESTMAX
WHERE (Id NOT IN
(SELECT TESTMAX_3.Id
FROM TESTMAX AS TESTMAX_3 INNER JOIN
TESTMAX AS TESTMAX_1 ON TESTMAX_3.NOM = TESTMAX_1.NOM
WHERE (TESTMAX_3.Value IN
(SELECT MAX(Value) AS Expr1
FROM TESTMAX AS TESTMAX_2
WHERE (NOM = TESTMAX_1.NOM)))
GROUP BY TESTMAX_3.Id, TESTMAX_3.NOM, TESTMAX_3.Value))
回答by Peter
Just group on Players.Nick alone, and select the first (min) of the description
只需对 Players.Nick 单独分组，然后选择描述的第一个（分钟）
SELECT Players.Nick, MIN(Reasons.Description), SUM(Marks.Value) AS Sum
FROM Marks INNER JOIN
Players ON Marks.PlayerID = Players.ID INNER JOIN
Reasons ON Marks.ReasonId = Reasons.ID
GROUP BY Players.Nick
ORDER BY Players.Nick, Sum DESC
that is if you always want the first without knowing it
也就是说，如果你总是想要第一个而不知道它
回答by rjmunro
In general, try using Subqueries rather than joining and grouping - it often makes SQL that is much easier to understand.
一般来说，尝试使用子查询而不是连接和分组——这通常会使 SQL 更容易理解。
SELECT Nick,
(SELECT Description from Reasons WHERE Reasons.ID = (
SELECT FIRST(Marks.ReasonId) from Marks WHERE Marks.PlayerID = Players.ID)
),
(SELECT SUM(Value) from Marks WHERE Marks.PlayerID = Players.ID)
回答by monojohnny
Is this an opportunity to use a 'HAVING' clause ? (You want to discriminate on an aggregate function - 'Sum') ?
这是使用“HAVING”子句的机会吗？（您想区分聚合函数 - 'Sum'）？
回答by Charles Bretana
(EDITED Based on edited question) Then, since you wish to filter based on the value of an aggregated column, what you need is a Having Clause.
（已编辑基于已编辑的问题）然后，由于您希望根据聚合列的值进行过滤，因此您需要的是 Have Clause。
SELECT p.Nick, r.Description, SUM(m.Value) Sum
FROM Marks m
JOIN Players p
ON m.PlayerID = p.ID
JOIN Reasons r
ON m.ReasonId = r.ID
GROUP BY p.Nick, r.Description
Having SUM(m.Value) =
(Select Max(Sum) From
(SELECT SUM(m.Value) Sum
FROM Marks mi
JOIN Players pi
ON mi.PlayerID = pi.ID
JOIN Reasons r i
ON mi.ReasonId = ri.ID
Where pi.Nick = p.Nick
GROUP BY pi.Nick, ri.Description))
Order By p.Nick, Sum Desc
回答by Kilanash
Curious. Only way I could get this to work was by using a temporary holding table in memory. (TSQL syntax)
好奇的。我可以让它工作的唯一方法是在内存中使用一个临时保存表。（TSQL 语法）
-- original test data
declare @sometable table ( foo int, bar int, value int )
insert into @sometable values (1, 5, 10)
insert into @sometable values (1, 4, 20)
insert into @sometable values (2, 1, 1)
insert into @sometable values (2, 1, 10)
insert into @sometable values (2, 1, 1)
insert into @sometable values (2, 2, 13)
insert into @sometable values (3, 4, 25)
insert into @sometable values (3, 5, 1)
insert into @sometable values (3, 1, 1)
insert into @sometable values (3, 1, 1)
insert into @sometable values (3, 1, 1)
insert into @sometable values (3, 1, 1)
insert into @sometable values (3, 1, 1)
-- temp table for initial aggregation
declare @t2 table (foo int, bar int, sums int)
insert into @t2
select foo, bar, sum(value)
from @sometable
group by foo, bar
-- final result
select foo, bar, sums
from @t2 a
where sums =
(select max(sums) from @t2 b
where b.foo = a.foo)
回答by Vinh
SQL Server 2005 you can use this:
SQL Server 2005 你可以使用这个：
declare @sometable table ( foo int, bar int, value int )
声明@sometable 表（ foo int, bar int, value int ）
insert into @sometable values (1, 5, 10) insert into @sometable values (1, 4, 20) insert into @sometable values (2, 1, 1) insert into @sometable values (2, 1, 10) insert into @sometable values (2, 1, 1) insert into @sometable values (2, 2, 13) insert into @sometable values (3, 4, 25) insert into @sometable values (3, 5, 1) insert into @sometable values (3, 1, 1) insert into @sometable values (3, 1, 1) insert into @sometable values (3, 1, 1) insert into @sometable values (3, 1, 1) insert into @sometable values (3, 1, 1)
插入@sometable 值(1, 5, 10) 插入@sometable 值(1, 4, 20) 插入@sometable 值(2, 1, 1) 插入@sometable 值(2, 1, 10) 插入@sometable 值(2, 1, 1) 插入@sometable 值(2, 2, 13) 插入@sometable 值(3, 4, 25) 插入@sometable 值(3, 5, 1) 插入@sometable值(3, 1, 1) 插入@sometable 值(3, 1, 1) 插入@sometable 值(3, 1, 1) 插入@sometable 值(3, 1, 1) 插入@sometable 值( 3, 1, 1)
-- temp table for initial aggregation declare @t2 table (foo int, bar int, sums int) insert into @t2 select foo, bar, sum(value) from @sometable group by foo, bar
-- 初始聚合的临时表声明@t2 表(foo int, bar int, sums int) insert into @t2 select foo, bar, sum(value) from @sometable group by foo, bar
select * from ( SELECT foo, bar, sums, ROW_NUMBER() OVER (PARTITION BY Foo ORDER BY Sums DESC) ROWNO FROM @t2) x where x.ROWNO = 1
select * from ( SELECT foo, bar, sums, ROW_NUMBER() OVER (PARTITION BY Foo ORDER BY Sums DESC) ROWNO FROM @t2) x where x.ROWNO = 1
