交互式查询

Question

是否有一种方法可以创建查询，当用户被提示输入值X以完成以下查询时：

SELECT X AS DISTANCE,
SUM(ABS(LOCX) <= X AND ABS(LOCY) <= X) AS QUANTITY,
COUNT(*) AS TOTAL,
CONCAT(AVG(ABS(LOCX) <= X AND ABS(LOCY) <= X)*100, '%') AS PERCENTAGE
FROM CUSTOMER;

Answer 1

假设您正在编写一个应用程序，其中用户提供输入，有多种方法来创建将这些值用作变量的查询--一种方法如下：

SET @t1=1, @t2=2, @t3:=4;
SELECT @t1, @t2;

来源：http://dev.mysql.com/doc/refman/5.5/en/user-variables.html

因此，对于您的特殊情况，将X的所有实例替换为用户定义的变量@X的MySQL语法，它将如下所示：

SET @X = user_input;
SELECT @X AS DISTANCE,
SUM(ABS(LOCX) <= @X AND ABS(LOCY) <= @X) AS QUANTITY,
COUNT(*) AS TOTAL,
CONCAT(AVG(ABS(LOCX) <= @X AND ABS(LOCY) <= @X)*100, '%') AS PERCENTAGE
FROM CUSTOMER;