sql - 返回每组一列最大值的行

Question

这个问题在这里已经有了答案:





Fetch the row which has the Max value for a column

(35 个回答)



Oracle SQL query: Retrieve latest values per group based on time [duplicate]

(2 个回答)



Get top results for each group (in Oracle)

(5 个回答)



GROUP BY with MAX(DATE) [duplicate]

(6 个回答)


3年前关闭。




我很难做到这一点而不至少搜索同一个表两次以获取最大行，然后获取该行的值。有问题的 table 很大，所以这是 Not Acceptable 。

这是我的表可能的样子:

SCORES
ID    ROUND    SCORE
1     1        3
1     2        6
1     3        2
2     1        10
2     2        12
3     1        6

我需要返回每个 ID 在最近一轮中获得的分数。也就是说，具有最大(回合)但不是最大分数的行。

OUTPUT:
ID   ROUND   SCORE
1    3       2
2    2       12
3    1       6

现在我有:

SELECT * FROM 
(SELECT id, round,
CASE WHEN (MAX(round) OVER (PARTITION BY id)) = round THEN score ELSE NULL END score
 FROM
 SCORES
 where id in (1,2,3)
) scorevals
WHERE
scorevals.round is not null;

这有效，但效率很低(我必须手动过滤掉所有这些行，当我首先应该无法抓取这些行时。)

我该怎么做才能获得正确的值？

Answer 1

这在没有子查询的情况下也是可能的:

SELECT DISTINCT
       id
      ,max(round) OVER (PARTITION BY id) AS round
      ,first_value(score) OVER (PARTITION BY id ORDER BY round DESC) AS score
FROM   SCORES
WHERE  id IN (1,2,3)
ORDER  BY id;

完全返回您所要求的内容。
关键在于 DISTINCT在窗口函数之后应用。

SQL Fiddle.

也许更快，因为它两次使用相同的窗口:

SELECT DISTINCT
       id
      ,first_value(round) OVER (PARTITION BY id ORDER BY round DESC) AS round
      ,first_value(score) OVER (PARTITION BY id ORDER BY round DESC) AS score
FROM   SCORES
WHERE  id IN (1,2,3)
ORDER  BY id;

否则做同样的事情。