MySQL 是否可以在 LIMIT 语法之后进行子查询?如果不是，为什么?

2022-01-23 00:00:00 sql limit syntax subquery mysql

我有 MySQL 服务器版本 5.1.53.我正在寻找一个小时来自己回答这个问题.包括在 http://dev.mysql.com/doc 阅读文档本身/refman/5.1/en/select.html

I have MySQL Server version 5.1.53. I was looking for an hour to answer this question by myself. Including read the documentation itself at http://dev.mysql.com/doc/refman/5.1/en/select.html

目前，我运行此查询.

SELECT dv2.timestamp 
FROM data_val AS dv2
WHERE dv2.timestamp > '2011-06-10 22:26:25' ORDER BY dv3.timestamp DESC 
LIMIT 1

然后我试图通过确定 MAX_QUERIES 减 1 的计算来消除 ORDER BY 语法.这样我可以写，

Then I was trying to eliminate the ORDER BY syntax by determining the calculation of MAX_QUERIES minus 1. By doing that I could write,

SELECT  (COUNT(*)-1) total 
FROM data_val AS dv2a
WHERE dv2a.timestamp > '2011-06-10 22:26:13'

最后查询变成了，

SELECT dv2.timestamp 
FROM data_val AS dv2
WHERE dv2.timestamp > '2011-06-10 22:26:13' 
LIMIT (
     SELECT  (COUNT(*)-1) total 
     FROM data_val AS dv2a
     WHERE dv2a.timestamp > '2011-06-10 22:26:13'
     ), 1

错误是:

#1064 - 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以在第 4 行的 '( SELECT (COUNT(*)-1) total FROM data_val AS dv2a ' 附近使用正确的语法

我还尝试将子查询放在 OFFSET 语法之后.但仍然错误.

I also tried to put the subquery after OFFSET syntax. but still error.

您知道为什么我的子查询不起作用吗?

Do you have any idea why my sub-query doesn't work?

我需要 short 的技术细节，简单，以及干净的解释.

I need technical details with short, simple, and clean explanation.

推荐答案

来自 MySQL 手册:http://dev.mysql.com/doc/refman/5.5/en/select.html

From the MySQL manual: http://dev.mysql.com/doc/refman/5.5/en/select.html

LIMIT 子句可用于限制 SELECT 语句返回的行数.LIMIT 接受一个或两个数字参数，它们都必须是非负整数常量，但以下情况除外:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:

在准备好的语句中，LIMIT 参数可以使用?占位符标记.

Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.

在存储程序中，LIMIT 参数可以使用整数值的例程参数或 MySQL 5.5.6 起的局部变量来指定.

Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables as of MySQL 5.5.6.

MySQL 查询优化器需要在运行查询之前将限制参数解析为常量，否则它将不知道要返回多少行.

The MySQL query optimizer needs to resolve the limit parameters to a constant before running the query, or it will not know how many rows to return.

相关文章