如何使用子查询定义Mysql SELECT LIMIT偏移量？
我一直在尝试“动态”定义查询的偏移量。
但是，当执行这个查询时，我总是会得到一个你的 SQL 语法有错误；
当我用一个数字替换子查询时，它确实工作正常。具有这种形状的查询是否有问题？
SELECT LengthOfStay
FROM table1
LIMIT (SELECT CAST(COUNT(DISTINCT(LengthOfStay)) / 2 AS SIGNED) FROM table1 t1), 2;
诗。我对它进行了转换，这样我就可以确保它是一个整数。
I have been trying to define "dynamically" the offset of a query.
But when executing this query I always end up with a You have an error in your SQL syntax;
When I do remplace the subquery by a number it does work fine. Is there something wrong in a query that has this shape?
SELECT LengthOfStay
FROM table1
LIMIT (SELECT CAST(COUNT(DISTINCT(LengthOfStay)) / 2 AS SIGNED) FROM table1 t1), 2;
Ps. I casted it so I can make sure it's an integer.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您不能使用子查询作为
LIMIT参数。限制参数应为INTEGER。你的子查询返回，嗯......基本上，一个表。无论如何，这个查询试图实现什么目的？这个查询对我来说没有任何意义......
You can not use a subquery as a
LIMITargument. Limit argument should be anINTEGER. Your subquery returns, well... basically, a table.What are trying to achieve by this query anyway? This query does not make any sense to me...
正如@michal 指出的那样，答案就在 How to make limit offset仅使用 (My)SQL 动态
基本上，如果您不在存储过程或准备好的语句中，则无法执行此操作。
如果您有一个存储过程，只需分配一个变量“内部”选择的值并在实际语句中使用它。
如果您有准备好的声明，请使用“limit ?, ?”并设置调用中的值。
As @michal points out the answer is in How to make limit offset dynamic using only (My)SQL
Basically if you're not in a stored procedure or a prepared statement you can't do it.
If you have a stored procedure just assign a variable the value of your "inner" select and use that on the real statement.
If you have a prepared statement use "limit ?, ?" and set the values from the call.