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;
但是当执行这个查询时,我总是最终得到一个你在SQL语法中有错误;
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.
PS。我投了它,所以我可以确保它是一个整数。
2 个解决方案
#1
2
You can not use a subquery as a LIMIT argument. Limit argument should be an INTEGER. Your subquery returns, well... basically, a table.
您不能将子查询用作LIMIT参数。限制参数应该是INTEGER。你的子查询返回,基本上,一个表。
What are trying to achieve by this query anyway? This query does not make any sense to me...
无论如何,这个查询试图实现什么?这个查询对我没有任何意义......
#2
0
As @michal points out the answer is in How to make limit offset dynamic using only (My)SQL
正如@michal所指出的,答案在于如何仅使用(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.
如果您有准备好的声明,请使用“限制?,?”并设置来自呼叫的值。
#1
2
You can not use a subquery as a LIMIT argument. Limit argument should be an INTEGER. Your subquery returns, well... basically, a table.
您不能将子查询用作LIMIT参数。限制参数应该是INTEGER。你的子查询返回,基本上,一个表。
What are trying to achieve by this query anyway? This query does not make any sense to me...
无论如何,这个查询试图实现什么?这个查询对我没有任何意义......
#2
0
As @michal points out the answer is in How to make limit offset dynamic using only (My)SQL
正如@michal所指出的,答案在于如何仅使用(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.
如果您有准备好的声明,请使用“限制?,?”并设置来自呼叫的值。