SQL 查询第 n 个最近更新

发布于 2024-09-16 11:09:41 字数 318 浏览 7 评论 0原文

我有一个修订表，我需要能够进行第 n 个最新更新。我有一个查询可以提供实体的最新修订记录，但我需要一个查询来获取第 n 个最新修订记录。

revisions
--+---------+--------+----------+-------
id|entity_id|contents|revisor_fk|revised

查询必须采用输入 0 到 n。如果输入为 0，则表示是最新的，1 表示回溯的一个修订或第二个最近的修订，2 表示回溯的修订或第三个最近的修订，依此类推。如果输入回溯更多修订，则该实体应返回修订没有行。

有什么想法吗？

原文

I have a revision table and I need to be able the nth most recent update. I have a query that gives me the most recent revision record of enitity, but I need one for the nth most recent revision record.

revisions
--+---------+--------+----------+-------
id|entity_id|contents|revisor_fk|revised

The query must take input 0 to n. If the input is 0 it is the most recent, 1 is one revision back or second most recent, 2 is to revisions back or the 3rd most recent, etc. And if the input is more revisions back then the entity has revisions it should return no rows.

Any thoughts?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

静谧 2024-09-23 11:09:41

假设 revised 是时间戳

SELECT fields
FROM revisions
WHERE entity_id = :your_entity_id
ORDER BY revised DESC
LIMIT :which_revision, 1

Assuming revised is the timestamp

SELECT fields
FROM revisions
WHERE entity_id = :your_entity_id
ORDER BY revised DESC
LIMIT :which_revision, 1

回复收藏 0 原文

冰魂雪魄 2024-09-23 11:09:41

在2937755遇到了这个

SELECT *
  FROM revisions a
 WHERE (4) = (select count(*)
                from revisions b
               where b.`revised` > a.`revised`)AND
`entity_id` = 1

但是@Matti Virkkunen 的解决方案要好得多。

Ran across this at 2937755

SELECT *
  FROM revisions a
 WHERE (4) = (select count(*)
                from revisions b
               where b.`revised` > a.`revised`)AND
`entity_id` = 1

But @Matti Virkkunen's solution is much nicer.

回复收藏 0 原文

~没有更多了~