限制最小最大范围值的SQL查询语法

I am new here. I have some issue in my sql query. I am writing sql query in my java code that gets three values from the user (limit, min, max) and show the records in between the minimum and maximum range. Such as if the user want to see 10 records and in between 5 to 100 transaction value. i am struggling in figuring out the correct syntax of the query. My query is

我是新来的。我的SQL查询中有一些问题。我在我的java代码中编写sql查询,它从用户获取三个值(limit,min,max)并显示最小和最大范围之间的记录。如果用户想要查看10条记录并且在5到100之间的交易值。我正在努力弄清楚查询的正确语法。我的疑问是

PreparedStatement statement = con.prepareStatement(""
                 + "Select Transaction_ID, Transaction_Value, Transaction_Type, Description  "
                 + "from Banking "
                 + "limit ? "
                 + "where MIN(Transaction_Value) = ? AND "
                 + "MAX(Transaction_Value) = ? ");

        //setting the limit of records, minimum and maximum values specified by the user
        statement.setInt(1, num);
        statement.setInt(2, min);
        statement.setInt(3, max);

        ResultSet result = statement.executeQuery();

When I execute this query, it prompt an error message that

当我执行此查询时,它会提示一条错误消息

Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'MIN(Transaction_Value) = 5 AND MAX(Transaction_Value) = 100' at line 1..

Your help will be really appreciated. I am really very much struggling. I have used simple queries before but dot know how to fix that error. Thank you so much in advance.

非常感谢您的帮助。我真的非常挣扎。我之前使用过简单查询,但dot知道如何修复该错误。非常感谢你提前。

2 个解决方案

#1

min and max are just range values in your query - there's no need to apply aggregate functions. Additionally, the limit clause should come after the where clause:

min和max只是查询中的范围值 - 不需要应用聚合函数。另外,limit子句应该在where子句之后:

SELECT Transaction_ID, Transaction_Value, Transaction_Type, Description  FROM   Banking
WHERE  Transaction_Value >= ? AND
       Transaction_Value <= ? 
LIMIT  ?

#2

....WHERE Transaction_Value BETWEEN 5 AND 100;

.... WHERE Transaction_Value在5和100之间;

MIN and MAX should be always used in a GROUP BY clause for more than 1 row

MIN和MAX应始终在GROUP BY子句中使用多于1行

SELECT MAX(daily_typing_pages)
     FROM employee_tbl;

SELECT id, name, MAX(daily_typing_pages)
    FROM employee_tbl GROUP BY name;

#1