MySQL是否可以使用LIMIT语法进行子查询?如果没有，为什么? [英] MySQL Is doing subquery after LIMIT syntax possible? If not, why?

问题描述

我有MySQL Server版本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 - 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 (COUNT(*)-1) total FROM data_val AS dv2a ' at line 4

我还尝试将子查询放在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.

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

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.

这篇关于MySQL是否可以使用LIMIT语法进行子查询?如果没有，为什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！