MySQL 通过选择 rownum 作为起始值来限制选择 [英] MySQL Limit selection by selecting rownum as start value

问题描述

我得到了一个带有 ID 列的表，该列被定义为增量.该表的行由更复杂的 mysql 语句过滤，如下所示(SELECT * FROM)，因此我不能使用 ID 作为 rownum.我想要的是在过滤后限制选择，从给定的 ID(例如 5)开始，在这种情况下，它可能是第 1 行，并在以下 10 行之后结束.

I got a table with an ID column which is defined as incremental. The rows of this table are filtered by a more complex mysql statement as shown here (SELECT * FROM) therefore I can't use the ID as rownum. What I want is to limit the selection, after it is filtered, starting with a given ID (such as 5) which could be row 1 and ending after the following 10 rows in this case.

这部分声明有效..

SELECT temp.rank 
FROM (
  SELECT @rownum := @rownum+1 rank, f.* FROM function f, (SELECT @rownum :=0) r
) temp
WHERE IDFunc = 5

但是包括 LIMIT 在内的复杂的不是..

but the complex one including LIMIT not..

SELECT *
FROM function 
LIMIT (
  SELECT temp.rank 
  FROM (
    SELECT @rownum := @rownum+1 rank, f.* FROM function f, (SELECT @rownum :=0)r
  ) temp
  WHERE IDFunc = 5
), 10

为什么?

推荐答案

同理

mysql> select * from t limit (select 1);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(select 1)' at line 1

失败:mysql 似乎不支持限制中的表达式.

fails: mysql does not seem to support expressions in the limit.

但是您可以使用局部变量，这可能会满足您的需求.参见 http://dev.mysql.com/doc/refman/5.1/en/select.html 语法，特别是关于 LIMIT 语法的部分.

But you can use local variables and that might give you what you need. See http://dev.mysql.com/doc/refman/5.1/en/select.html syntax, and especially section on the LIMIT syntax.

这篇关于MySQL 通过选择 rownum 作为起始值来限制选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！