SQL 查询中的多个最大值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1673802/
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
Multiple max values in a query
提问by kurast
I know the title does not sound very descriptive, but it is the best I could think of:
我知道标题听起来不太具有描述性，但它是我能想到的最好的：
I have this table
我有这张桌子
ID BDATE VALUE 28911 14/4/2009 44820 28911 17/4/2009 32240 28911 20/4/2009 30550 28911 22/4/2009 4422587,5 28911 23/4/2009 4441659 28911 24/4/2009 7749594,67 38537 17/4/2009 58280 38537 20/4/2009 137240 38537 22/4/2009 81098692 38605 14/4/2009 2722368 38605 20/4/2009 5600 38605 22/4/2009 1625400 38605 23/4/2009 6936575
which is in fact a very complicated query encapsulated in a view, but it is not of the matter now.
这实际上是封装在视图中的一个非常复杂的查询，但现在不重要了。
I would like to have for each ID, the row containing the highest BDate. In this example, this would be the result.
我想为每个 ID，包含最高 BDate 的行。在这个例子中，这就是结果。
ID BDATE VALUE 28911 24/4/2009 7749594,67 38537 22/4/2009 81098692 38605 23/4/2009 6936575
I have already tried
我已经试过了
select id, max(bdate), value from myview group by id, value
but then it returns all the rows, because for each the value collumn is different. This query is designed in Oracle v10, and I am eligible to use only select queries and not to create procedures.
但随后它返回所有行，因为对于每个值列是不同的。此查询是在 Oracle v10 中设计的，我有资格仅使用选择查询而不是创建过程。
回答by APC
We can use multiply columns in an IN clause:
我们可以在 IN 子句中使用多列：
select id, bdate, value
from myview
where (id, bdate) in
(select id, max(bdate)
from myview group by id)
/
回答by Vincent Malgrat
you can use the MAX...KEEP(DENSE_RANK FIRST...)construct:
你可以使用MAX...KEEP(DENSE_RANK FIRST...)构造：
SQL> SELECT ID,
2 MAX(bdate) bdate,
3 MAX(VALUE) KEEP(DENSE_RANK FIRST ORDER BY bdate DESC) VALUE
4 FROM DATA
5 GROUP BY ID;
ID BDATE VALUE
---------- ----------- ----------
28911 24/04/2009 7749594,67
38537 22/04/2009 81098692
38605 23/04/2009 6936575
This will be as efficient as the analytics method suggested by Majkel(no self-join, a single pass on the data)
回答by Andomar
You can use an INNER JOIN to filter out only the maximum rows:
您可以使用 INNER JOIN 仅过滤掉最大行数：
select t.*
from YourTable t
inner join (
select id, max(bdate) as maxbdate
from YourTable
group by id
) filter
on t.id = filter.id
and t.bdate = filter.maxbdate
This prints:
这打印：
id bdate value
38605 2009-04-23 6936575
38537 2009-04-22 81098692
28911 2009-04-24 7749594.67
Note that this will return multiple rows for an id which has multiple values with the same bdate.
请注意，这将为具有相同 bdate 的多个值的 id 返回多行。
回答by Majkel
You can use analytics:
您可以使用分析：
select
id, bdate, value
from
(
select
id, bdate, value, max( bdate ) over ( partition by id ) max_bdate
from
myview
)
where
bdate = max_bdate
回答by Anthony Mills
select a.* from myview a, (select id, max(bdate) from myview group by id) b
where a.id = b.id and a.bdate = b.bdate
回答by Heinzi
SELECT id, bdate, value FROM myview
WHERE (id, bdate) IN (SELECT id, MAX(bdate) FROM myview GROUP BY id)
(untested... I don't have Oracle available right now...)
（未经测试...我现在没有可用的 Oracle...）
