android - SQLite rawQuery 不返回带有整数参数的任何结果

Question

Android 中 SQLite 数据库的以下 rawQuery 不返回任何结果:

String query = "SELECT * FROM " +
    "(SELECT MATCH_ID, PLAYER_ID, SUM(SHOTS) AS SHOT_SUM, SUM(MAX(GOAL - SHOTS + 2, 0)) AS SCORE_SUM, COUNT(SHOTS) AS SHOT_COUNT " +
        "FROM RESULT R INNER JOIN MATCH M ON M.ID = R.MATCH_ID " +
        "INNER JOIN PLAYER P ON R.PLAYER_ID = P.ID " +
            "WHERE R.IS_ACTIVE=1 AND SHOTS>0 AND P.IS_ACTIVE=1 AND NOT_COMPLETE=0 " +
            "GROUP BY MATCH_ID, PLAYER_ID) " +
    "WHERE SHOT_COUNT=? ORDER BY SHOT_SUM;";

Cursor cursor = this.db.rawQuery(query, new String[] { String.valueOf(9) });

当我用硬编码值替换 where 子句中的参数时，它按预期工作:

String query = "SELECT * FROM " +
    "(SELECT MATCH_ID, PLAYER_ID, SUM(SHOTS) AS SHOT_SUM, SUM(MAX(GOAL - SHOTS + 2, 0)) AS SCORE_SUM, COUNT(SHOTS) AS SHOT_COUNT " +
        "FROM RESULT R INNER JOIN MATCH M ON M.ID = R.MATCH_ID " +
        "INNER JOIN PLAYER P ON R.PLAYER_ID = P.ID " +
            "WHERE R.IS_ACTIVE=1 AND SHOTS>0 AND P.IS_ACTIVE=1 AND NOT_COMPLETE=0 " +
            "GROUP BY MATCH_ID, PLAYER_ID) " +
    "WHERE SHOT_COUNT=9 ORDER BY SHOT_SUM;";

Cursor cursor = this.db.rawQuery(query, new String[] { });

我做错了什么？

Answer 1

因为 SHOT_COUNT 是一个计算列，它与您作为参数传递的字符串值不匹配。替换为:

COUNT(SHOTS) AS SHOT_COUNT

用这个:

CAST(COUNT(SHOTS) AS TEXT) AS SHOT_COUNT