可以将LIMIT与子查询结果一起使用吗? [英] Is it possible to use LIMIT with a subquery result?

问题描述

当需要有序集的最后一行时，通常会创建派生表并重新排序.例如，要以自动递增的id返回表的最后3个元素:

When the last rows of an ordered set are required, it is usual to create a derived table and reorder. For example, to return the last 3 elements of a table with an auto incremented id:

SELECT * FROM (
    SELECT * FROM table ORDER BY id DESC LIMIT 3
) t ORDER BY t.id

由于LIMIT也可以具有偏移量，因此如果我们提前知道此查询的行数(例如10)，则可以实现相同的结果:

Since LIMIT can also have an offset, the same result could be achieved if we know the number of lines (say 10) in advance with this query:

SELECT * FROM table ORDER BY id LIMIT 3 OFFSET 7

是否可以对表进行count(*)子查询并使用该数字动态构建LIMIT?

Is it possible to run a subquery to count(*) a table and dynamically build a LIMIT using that number?

SELECT * FROM table ORDER BY id LIMIT 3 OFFSET [ select count() -3 ]

推荐答案

否，无法指定动态偏移量.

No, it isn't possible to specify a dynamic offset.

使用子查询的原始查询是最简单的方法.

Your original query with a subquery is the easiest way to do this.

这篇关于可以将LIMIT与子查询结果一起使用吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！