SQL Oracle：获得组的最大值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9220944/
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
Oracle: getting maximum value of a group?
提问by Mark Harrison
Given a table like this, what query will the most recent calibration information for each monitor? In other words, I want to find the maximum date value for each of the monitors. Oracle-specific functionality is fine for my application.
给定这样的表格，每个显示器的最新校准信息将查询什么？换句话说，我想找到每个监视器的最大日期值。Oracle 特定的功能非常适合我的应用程序。
monitor_id calibration_date value
---------- ---------------- -----
1 2011/10/22 15
1 2012/01/01 16
1 2012/01/20 17
2 2011/10/22 18
2 2012/01/02 19
The results for this example would look like this:
此示例的结果如下所示：
1 2012/01/20 17
2 2012/01/02 19
回答by Justin Cave
I'd tend to use analytic functions
我倾向于使用分析函数
SELECT monitor_id,
host_name,
calibration_date,
value
FROM (SELECT b.monitor_id,
b.host_name,
a.calibration_date,
a.value,
rank() over (partition by b.monitor_id order by a.calibration_date desc) rnk
FROM table_name a,
table_name2 b
WHERE a.some_key = b.some_key)
WHERE rnk = 1
You could also use correlated subqueries though that will be less efficient
您也可以使用相关子查询，但效率会降低
SELECT monitor_id,
calibration_date,
value
FROM table_name a
WHERE a.calibration_date = (SELECT MAX(b.calibration_date)
FROM table_name b
WHERE a.monitor_id = b.monitor_id)
回答by Jeffrey Kemp
My personal preference is this:
我个人的偏好是这样的：
SELECT DISTINCT
monitor_id
,MAX(calibration_date)
OVER (PARTITION BY monitor_id)
AS latest_calibration_date
,FIRST_VALUE(value)
OVER (PARTITION BY monitor_id
ORDER BY calibration_date DESC)
AS latest_value
FROM mytable;
A variation would be to use the FIRST_VALUEsyntax for latest_calibration_dateas well. Either way works.
一个变体是也使用FIRST_VALUE语法latest_calibration_date。无论哪种方式都有效。
回答by J Cooper
The window functions solution shouldbe the most efficient and result in only one table or index scan. The one I am posting here i think wins some points for being intuitive and easy to understand. I tested on SQL server and it performed 2nd to window functions, resulting in two index scans.
窗口函数解决方案应该是最有效的，并且只进行一个表或索引扫描。我在这里发布的那个我认为因为直观和易于理解而赢得了一些分数。我在 SQL 服务器上进行了测试，它执行了第二个窗口函数，导致了两次索引扫描。
SELECT T1.monitor_id, T1.calibration_date, T1.value
FROM someTable AS T1
WHERE NOT EXISTS
(
SELECT *
FROM someTable AS T2
WHERE T2.monitor_id = T1.monitor_id AND T2.value > T1.value
)
GROUP BY T1.monitor_id, T1.calibration_date, T1.value
And just for the heck of it, here's another one along the same lines, but less performing (63% cost vs 37%) than the other (again in sql server). This one uses a Left Outer Join in the execution plan where as the first one uses an Anti-Semi Merge Join:
只是为了它，这里有另一个与此相同的方法，但性能（成本为 63% 对 37%）低于另一个（再次在 sql server 中）。这个在执行计划中使用左外连接，而第一个使用反半合并连接：
SELECT T1.monitor_id, T1.calibration_date, T1.value
FROM someTable AS T1
LEFT JOIN someTable AS T2 ON T2.monitor_id = T1.monitor_id AND T2.value > T1.value
WHERE T2.monitor_id IS NULL
GROUP BY T1.monitor_id, T1.calibration_date, T1.value
回答by Sergey Benner
select monitor_id, calibration_date, value
from table
where calibration_date in(
select max(calibration_date) as calibration_date
from table
group by monitor_id
)
