oracle 返回每组一列最大值的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10342405/
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
Return row with the max value of one column per group
提问by Jeremy
I am having a hard time doing this without searching the same table at least twice in order to grab the max row, and then grab the value for that row. The table in question is quite big so this is unacceptable.
我很难做到这一点而不至少搜索同一个表两次以获取最大行，然后获取该行的值。有问题的桌子很大，所以这是不可接受的。
Here is what my table might look like:
这是我的表可能的样子：
SCORES
ID ROUND SCORE
1 1 3
1 2 6
1 3 2
2 1 10
2 2 12
3 1 6
I need to return the score that each ID got in the most recent round. That is, the row with the max (round), but not the max score.
我需要返回每个 ID 在最近一轮中获得的分数。也就是说，具有最大值（回合）但不是最大分数的行。
OUTPUT:
ID ROUND SCORE
1 3 2
2 2 12
3 1 6
Right now I have:
现在我有：
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;
This works, but is pretty inefficient (I have to manually filter out all of these rows, when I should just be able to not grab those rows in the first place.)
这有效，但效率非常低（我必须手动过滤掉所有这些行，当我首先应该无法获取这些行时。）
What can I do to get the right values?
我该怎么做才能获得正确的值？
采纳答案by Erwin Brandstetter
This is also possible without subquery:
这在没有子查询的情况下也是可能的：
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;
Returns exactly what you asked for.
The crucial point is that DISTINCTis applied afterwindow functions.
完全返回您所要求的内容。
关键是在窗函数之后DISTINCT应用。
Maybe faster because it uses the same window twice:
也许更快，因为它两次使用同一个窗口：
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;
Otherwise doing the same.
否则做同样的事情。
回答by Justin Cave
You're on the right track using analytic functions. But you probably want something like this with the rankfunction
您使用分析函数走在正确的轨道上。但你可能想要这样的rank功能
SELECT *
FROM (SELECT a.*,
rank() over (partition by id order by round desc) rnk
FROM scores
WHERE id IN (1,2,3))
WHERE rnk = 1
If there can be ties (rows that have the same idand round) you may want to use the row_numberanalytic function instead of rank-- that will arbitrarily pick one of the two tied rows to have a rnkof 1 rather than returning both as rankwould.
如果可能存在联系（具有相同id和的行round），您可能希望使用row_number分析函数而不是rank- 它将任意选择两个绑定行之一使其 arnk为 1，而不是像rank那样返回两者。
If you wanted to use the MAXanalytic function, you could also do something like
如果你想使用MAX解析函数，你也可以做类似的事情
SELECT *
FROM (SELECT a.*,
MAX(round) OVER (partition by id) max_round
FROM scores
WHERE id IN (1,2,3))
WHERE round = max_round
回答by René Nyffenegger
For this kind of problems, I tend to use the max...keep...dense_rankconstruct:
对于此类问题，我倾向于使用以下max...keep...dense_rank结构：
select
id,
max(round) round,
max(score) keep (dense_rank last order by round) score
from
tq84_scores
group by
id;
