mysql - SQL查询-基于子查询的限制

Question

我试图获取标志字段等于 1 的条目的上半部分。我尝试使用变量来保存限制值，如下所示

set @v1:=(select ceil(count(*)/2) as top_half from my_table
where flagged=1);

select * from my_table where flagged=1 order by 21_day_probability limit @v1;

但这不起作用。有什么建议吗？

谢谢

Answer 1

如 SELECT 语法中所述:

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:

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

• Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables.

因此 LIMIT 参数永远不能是 user-defined variables 。如上所述，您的选择是使用:

1. 准备好的声明

   PREPARE stmt FROM '
     select * from my_table where flagged=1 order by 21_day_probability limit ?
   ';
   EXECUTE stmt USING @v1;
   DEALLOCATE PREPARE stmt;
   

2. 存储程序中的参数/局部变量:

   CREATE PROCEDURE foo(_l INT)
     select * from my_table where flagged=1 order by 21_day_probability limit _l
   ;
   CALL foo(@v1);