Oracle SQL 最后 n 条记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26361685/
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 SQL last n records
提问by Phil
i have read tons of articles regarding last n records in Oracle SQL by using rownum functionality, but on my case it does not give me the correct rows.
我通过使用 rownum 功能阅读了大量关于 Oracle SQL 中最后 n 条记录的文章，但就我而言，它没有给我正确的行。
I have 3 columns in my table: 1) message (varchar), mes_date (date) and mes_time (varchar2).
我的表中有 3 列：1）消息（varchar）、mes_date（日期）和 mes_time（varchar2）。
Inside lets say there is 3 records:
里面可以说有3条记录：
Hello world | 20-OCT-14 | 23:50
World Hello | 21-OCT-14 | 02:32
Hello Hello | 20-OCT-14 | 23:52
I want to get the last 2 records ordered by its date and time (first row the oldest, and second the newest date/time)
我想获取按日期和时间排序的最后 2 条记录（第一行是最旧的，第二行是最新的日期/时间）
i am using this query:
我正在使用这个查询：
SELECT *
FROM (SELECT message
FROM messages
ORDER
BY MES_DATE, MES_TIME DESC
)
WHERE ROWNUM <= 2 ORDER BY ROWNUM DESC;
Instead of getting row #3 as first and as second row #2 i get row #1 and then row #3
而不是将第 3 行作为第一行和第二行 #2 我得到第 1 行然后是第 3 行
What should i do to get the older dates/times on top follow by the newest?
我应该怎么做才能让较旧的日期/时间紧随最新的？
回答by DirkNM
Maybe that helps:
也许这有帮助：
SELECT *
FROM (SELECT message,
mes_date,
mes_time,
ROW_NUMBER() OVER (ORDER BY TO_DATE(TO_CHAR(mes_date, 'YYYY-MM-DD') || mes_time, 'YYYY-MM-DD HH24:MI') DESC) rank
FROM messages
)
WHERE rank <= 2
ORDER
BY rank
回答by evenro
I am really sorry to disappoint - but in Oracle there's no such thing as "the last two records".
我真的很抱歉让您失望 - 但在 Oracle 中没有“最后两条记录”这样的东西。
The table structure does not allocate data at the end, and does not keep a visible property of time (the only time being held is for the sole purpose of "flashback queries" - supplying results as of point in time, such as the time the query started...).
表结构最后不分配数据，也不保留时间的可见属性（唯一保留的时间仅用于“闪回查询”-提供时间点的结果，例如查询开始...）。
The last inserted record is not something you can query using the database.
最后插入的记录不是您可以使用数据库查询的内容。
What can you do? You can create a trigger that orders the inserted records using a sequence, and select based on it (so SELECT * from (SELECT * FROM table ORDER BY seq DESC) where rownum < 3) - that will assure order only if the sequence CACHE value is 1.
你能做什么？您可以创建一个触发器，使用序列对插入的记录进行排序，并根据它进行选择（so SELECT * from (SELECT * FROM table ORDER BY seq DESC) where rownum < 3） - 只有当序列 CACHE 值为 1 时才能确保顺序。
Notice that if the column that contains the message date does not have many events in a second, you can use that column, as the other solution suggested - e.g. if you have more than 2 events that arrive in a second, the query above will give you random two records, and not the actual last two.
请注意，如果包含消息日期的列在一秒钟内没有很多事件，您可以使用该列，正如其他解决方案所建议的那样 - 例如，如果您有一秒钟内到达 2 个以上的事件，则上面的查询将给出你随机两个记录，而不是实际的最后两个。
AGAIN - Oracle will not be queryable for the last two rows inserted since its data structure do not managed orders of inserts, and the ordering you see when running "SELECT *" is independent of the actual inserts in some specific cases.
再次 - Oracle 将无法查询插入的最后两行，因为其数据结构不管理插入顺序，并且在某些特定情况下，您在运行“SELECT *”时看到的顺序与实际插入无关。
If you have any questions regarding any part of this answer - post it down here, and I'll focus on explaining it in more depth.
如果您对此答案的任何部分有任何疑问 - 请在此处发布，我将专注于更深入地解释它。
