SQL 选择具有与条件匹配的最新时间戳的行的最佳方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/285477/
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
Best way to select the row with the most recent timestamp that matches a criterion
提问by George Mauer
This is something that comes up so often I almost stopped thinking about it but I'm almost certain that I'm not doing this the best way.
这是经常出现的事情，我几乎停止思考它，但我几乎可以肯定我不是最好的方法。
The question: Suppose you have the following table
问题：假设你有下表
CREATE TABLE TEST_TABLE
(
ID INTEGER,
TEST_VALUE NUMBER,
UPDATED DATE,
FOREIGN_KEY INTEGER
);
What is the best way to select the TEST_VALUE associated with the most recently updated row where FOREIGN_KEY = 10?
选择与 FOREIGN_KEY = 10 的最近更新的行关联的 TEST_VALUE 的最佳方法是什么？
EDIT:Let's make this more interesting as the answers below simply go with my method of sorting and then selecting the top row. Not bad but for large returns the order by would kill performance. So bonus points: how to do it in a scalable manner (ie without the unnecessary order by).
编辑：让我们让这更有趣，因为下面的答案只是按照我的排序方法然后选择顶行。不错，但对于大额回报，order by 会降低性能。所以加分点：如何以可扩展的方式做到这一点（即没有不必要的 order by）。
回答by Justin Cave
Analytic functions are your friends
分析函数是你的朋友
SQL> select * from test_table;
ID TEST_VALUE UPDATED FOREIGN_KEY
---------- ---------- --------- -----------
1 10 12-NOV-08 10
2 20 11-NOV-08 10
SQL> ed
Wrote file afiedt.buf
1* select * from test_table
SQL> ed
Wrote file afiedt.buf
1 select max( test_value ) keep (dense_rank last order by updated)
2 from test_table
3* where foreign_key = 10
SQL> /
MAX(TEST_VALUE)KEEP(DENSE_RANKLASTORDERBYUPDATED)
-------------------------------------------------
10
You can also extend that to get the information for the entire row
您还可以扩展它以获取整行的信息
SQL> ed
Wrote file afiedt.buf
1 select max( id ) keep (dense_rank last order by updated) id,
2 max( test_value ) keep (dense_rank last order by updated) test_value
,
3 max( updated) keep (dense_rank last order by updated) updated
4 from test_table
5* where foreign_key = 10
SQL> /
ID TEST_VALUE UPDATED
---------- ---------- ---------
1 10 12-NOV-08
And analytic approaches are generally pretty darned efficient.
分析方法通常非常有效。
I should also point out that analytic functions are relatively new, so if you are on something earlier than 9.0.1, this may not work. That's not a huge population any more, but there are always a few folks stuck on old versions.
我还应该指出，分析函数相对较新，因此如果您使用的是 9.0.1 之前的版本，这可能不起作用。这不再是一个庞大的人口，但总有一些人坚持使用旧版本。
回答by Tomalak
Either use a sub-query
要么使用子查询
WHERE updated = (SELECT MAX(updated) ...)
or select the TOP 1 record with
或选择 TOP 1 记录
ORDER BY updated DESC
In Oracle syntax this would be:
在 Oracle 语法中，这将是：
SELECT
*
FROM
(
SELECT * FROM test_table
ORDER BY updated DESC
)
WHERE
ROWNUM = 1
回答by Gary Myers
Firstly, you will always need to look at all the rows with that foreign key, and find the one with the highest UPDATED value...which means a MAX or ORDER BY. The efficiency of the comparison is partly up to the optimizer, so will depend on your Oracle version. Your data structures may have a greater impact on actual performance though. An index on FOREIGN_KEY, UPDATED DESC, TEST_VALUE would probably give the most scalable solution for querying as Oracle will normally be able to give the answer just accessing a single leaf block. There may be a detrimental impact on inserts as new records have to be inserted into that structure.
首先，您将始终需要查看具有该外键的所有行，并找到具有最高 UPDATED 值的行……这意味着 MAX 或 ORDER BY。比较的效率部分取决于优化器，因此将取决于您的 Oracle 版本。不过，您的数据结构可能会对实际性能产生更大的影响。FOREIGN_KEY、UPDATED DESC、TEST_VALUE 上的索引可能会提供最具扩展性的查询解决方案，因为 Oracle 通常能够仅访问单个叶块就给出答案。由于必须将新记录插入到该结构中，因此可能会对插入产生不利影响。
回答by George Mauer
The probably inferior way that I currently go about doing something like this is
我目前做这样的事情的可能较差的方式是
SELECT TEST_VALUE
FROM TEST_TABLE
WHERE ID = (
SELECT ID
FROM (
SELECT ID
FROM TEST_TABLE
WHERE FOREIGN_KEY = 10
ORDER BY UPDATED DESC
)
WHERE ROWNUM = 1
)
but please StackOverflow Geniuses, teach me some tricks
但请 StackOverflow 天才们，教我一些技巧
回答by Paul Morgan
SELECT TEST_VALUE
FROM TEST_TABLE
WHERE UPDATED = ( SELECT MAX(UPDATED)
FROM TEST_TABLE
WHERE FOREIGN_KEY = 10 )
AND FOREIGN-KEY = 10
AND ROWNUM = 1 -- Just in case records have the same UPDATED date
Rather that take the first record you could break a tie with the hightest ID or maybe least/largest TEST_VALUE.
而不是拿第一个记录，你可以打破与最高 ID 或最小/最大 TEST_VALUE 的平局。
An index of FOREIGN_KEY, UPDATED would help query performace.
FOREIGN_KEY、UPDATED 的索引将有助于查询性能。
回答by aaaantoine
Up until I read Justin Cave's answer, I've used the following pattern to grab the most recent records en masse.
在我阅读 Justin Cave 的回答之前，我一直使用以下模式来获取最新的记录。
WITH test_table_ranked AS (
SELECT
test_table.*,
ROW_NUMBER() OVER (
PARTITION BY foreign_key ORDER BY updated DESC
) AS most_recent
FROM
test_table
)
SELECT *
FROM test_table_ranked
WHERE most_recent = 1
-- AND foreign_key = 10
This query finds the most recent updates for each foreign key in the table. Although Justin's answer is faster when the key is known, this query also works in SQL Server.
此查询查找表中每个外键的最新更新。尽管在知道密钥时 Justin 的回答会更快，但此查询也适用于 SQL Server。
回答by EvilTeach
Performance will depend on what is indexed. Here is a method.
性能将取决于索引的内容。这里有一个方法。
WITH
ten AS
(
SELECT *
FROM TEST_TABLE
WHERE FOREIGH_KEY = 10
)
SELECT TEST_VALUE
FROM ten
WHERE UPDATED =
(
SELECT MAX(DATE)
FROM ten
)
回答by Jason Slocomb
There is an oracle SQL faq here that may help you:
这里有一个 oracle SQL 常见问题解答可以帮助您：
回答by WW.
select test_value
from
(
select test_value
from test_table
where foreign_key=10
order by updated desc
)
where rownum = 1
Oracle is smart enough to realize it only needs a single row from the inner select and it will do this efficiently.
Oracle 足够聪明，它意识到它只需要来自内部选择的一行，并且它会有效地做到这一点。
回答by FallenAvatar
wouldn't this work:
这行不通：
SELECT TOP 1 ID
FROM test_table
WHERE FOREIGN_KEY = 10
ORDER BY UPDATED DESC
no need for a subquery...
不需要子查询...
