如何在SQL查询中选择每个组的第一行？

Question

我有这个 SQL 查询：

SELECT   Foo, Bar, SUM(Values) AS Sum
FROM     SomeTable
GROUP BY Foo, Bar
ORDER BY Foo DESC, Sum DESC

这将导致类似于以下的输出：

47  1   100
47  0   10
47  2   10
46  0   100
46  1   10
46  2   10
44  0   2

我希望只保留每个Foo的第一行（以及它的最高Bar），并忽略其余行。

47  1   100
46  0   100
44  0   2

我该怎么做？

- mafu

---

重写了问题，简化了查询。除非更新以反映更改，否则某些答案可能不再有意义。 - mafu

这个回答是否解决了你的问题？在每个GROUP BY分组中选择第一行？ - Michael Freidgeim

这个后来被问到的问题确实是这个问题的副本;-) - mafu

Answer 1

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

- Anton Georgiev

Answer 2

这是一篇旧帖子，但我今天也遇到了同样的问题。我通过尝试多个查询来解决它。我正在使用SQL Compact 3.5和Visual Basic 2010。

这个示例是针对一个名为“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

如果您想删除其他行，也可以使用以下方法：

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))

- Gerauld

Answer 3

我认为使用Ansii风格的连接方式比子查询更易读，但是每个人都有自己的看法——我只是按照我们的DBAs所建议的做。如果您只想从查询中获取第一个结果，则可以使用rownum（如果使用oracle，则其他数据库可能有类似的东西）。当然，根据您想要完成的任务，HAVING子句也可能是合适的选择。

“HAVING用于对由GROUP BY创建的组执行类似于基本SQL语句中WHERE子句在行上执行的操作。 WHERE子句限制评估的行数。 HAVING子句限制返回的分组行。”

希望能对你有所帮助。

- tmeisenh

---

如果我理解正确的话，那么这实际上就是我想要的。 - mafu

Answer 4

只需按照 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

这就是如果你总是想要第一个而不知道它的方法

- Peter

---

我想要第三列中具有最高值的那一行。 - mafu

Answer 5

那么，既然您希望根据聚合列的值进行过滤，您需要使用Having子句。

  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

- Charles Bretana

---

我不想通过“X”来选择输出，而是根据第三行的值（请参见编辑）进行选择。 - mafu

Answer 6

一般来说，尝试使用子查询而不是连接和分组 - 这通常会使 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)

- rjmunro

Answer 7

好奇怪，我只能通过在内存中使用临时保持表才能使它正常工作。(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)

- Kilanash

Answer 8

在SQL Server 2005中，您可以使用以下代码：

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)

-- 用于初始聚合的临时表 declare @t2 table (foo int, bar int, sums int) insert into @t2 select foo, bar, sum(value) from @sometable group by foo, bar

从 @t2 中选择 *，并按 Sums 降序排序，然后根据 Foo 分组计算 ROWNO。最后从结果中选择 ROWNO 等于 1 的记录。

- Vinh