java在WHERE子句中使用行数

Question

我试图根据currentQuestion integer检索字符串值。然而，我在文档中读到，ROW_NUMBER不能在Java Derby的WHERE子句中使用。是否有其他功能允许我根据行号筛选结果？到目前为止，我已经写了这篇文章，尽管我在getQuestion字符串上遇到了错误

public String getQuestion(int currentQuestion) throws SQLException {
        Statement statement = connection.createStatement();

        String getQuestion = ("SELECT QUESTION FROM GAME_TABLE WHERE ROW_NUMBER = ?");
        PreparedStatement preStatement = connection.prepareStatement(getQuestion);
        preStatement.setString(1, Integer.toString(currentQuestion));
        ResultSet resultSet = preStatement.executeQuery();

        System.out.println("Question " + Integer.toString(currentQuestion) + " retrieved.");

        return resultSet.getString(1);
    }

Answer 1

您可以使用CTE这样的

WITH CTE As(
SELECT QUESTION, ROW_NUMBER() OVER (Arguments you want) As RN
FROM GAME_TABLE
)
SELECT *
FROM CTE
WHERE RN = ?;

或者，一个子查询，如：

SELECT T.*
FROM ( SELECT QUESTION, ROW_NUMBER() OVER (Arguments you want) As RN
FROM GAME_TABLE ) As T
WHERE T.RN = ?;

Answer 2

 Statement st = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
 String getQuestion = ("SELECT QUESTION FROM GAME_TABLE ");
 ResultSet resultSet = st.executeQuery(getQuestion);
 resultSet.absolute(currentQuestion);// moves the cursor to the row
 return resultSet.getString("Question");

当然，这个答案就像得到整张表后的处理一样