SQLite数据库无法返回最低总分值的问题求助

刚接触SQL查询，实现从SQLite数据库获取最低总分功能时遇到问题——当前查询返回的值并非数据库中的最低总分。

需求说明：

• 若多名玩家总分同为最低，只需显示其中任意一位
• 每次调用方法时更新结果，目前已在按钮点击事件及RunOnUiThread中执行该方法

DBHandler类代码 #

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHandler extends SQLiteOpenHelper {
    public static final String Name = "GolfScores";
    private static final int ver = 1;
    public static final String tableName = "guestScores";
    public static final String DBId = "id";
    public static final String DBName = "name";
    private static final String DBHole1 = "hole1";
    private static final String DBHole2 = "hole2";
    private static final String DBHole3 = "hole3";
    public static final String DBTotal = "totalScore";
    public static final String DBDate = "DatePlayed";

    public DBHandler(Context context) {
        super(context, Name, null, ver);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String query = "CREATE TABLE " + tableName + " ("
                + DBId + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                + DBName + " TEXT,"
                + DBHole1 + " TEXT,"
                + DBHole2 + " TEXT,"
                + DBHole3 + " TEXT,"
                + DBTotal + " TEXT,"
                + DBDate + " TEXT)" +
                " ";
        db.execSQL(query);
    }

    public void addNewPlayer(String playerName, String Hole1Score, String Hole2Score, String Hole3Score, String CourseScore, String DatePlayed) {
        try {
            SQLiteDatabase db = this.getWritableDatabase();
            ContentValues values = new ContentValues();

            values.put(DBName, playerName);
            values.put(DBHole1, Hole1Score);
            values.put(DBHole2, Hole2Score);
            values.put(DBHole3, Hole3Score);
            values.put(DBTotal, CourseScore);
            values.put(DBDate, DatePlayed);

            db.insert(tableName, null, values);
            db.close();
        } catch (Exception e) {
            Log.e("DBHandler", "Error adding player: " + e.getMessage());
        }
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + tableName);
        onCreate(db);
    }

    String TAG = "DBHandler";
    @SuppressLint("Range")
    public String getTableAsString(SQLiteDatabase db, String tableName) {
        Log.d(TAG, "getTableAsString called");
        String tableString = String.format("Table %s:\n", tableName);
        Cursor allRows  = db.rawQuery("SELECT * FROM " + tableName, null);
        if (allRows.moveToFirst() ){
            String[] columnNames = allRows.getColumnNames();
            do {
                for (String name: columnNames) {
                    tableString += String.format("%s: %s\n", name,
                            allRows.getString(allRows.getColumnIndex(name)));
                }
                tableString += "\n";

            } while (allRows.moveToNext());
        }

        return tableString;
    }
}

获取最低分的方法代码 #

private void getLowest() {
    TextView lowestScoreTextView = findViewById(R.id.lowestScoringPlayerTXT);

    DBHandler dbHandler = new DBHandler(Menu.this);
    SQLiteDatabase db = dbHandler.getReadableDatabase();

    Cursor cursor = db.rawQuery("SELECT " + DBHandler.DBName + "," + DBHandler.DBTotal + " FROM " + DBHandler.tableName + " ORDER BY " + DBHandler.DBTotal + " ASC LIMIT 1", null);

    if (cursor.moveToFirst()) {
        String playerName = cursor.getString(cursor.getColumnIndex(DBHandler.DBName));
        int totalScore = cursor.getInt(cursor.getColumnIndex(DBHandler.DBTotal));

        String resultString = "Player Name: " + playerName + "\nTotal Score: " + totalScore;

        lowestScoreTextView.setText(resultString);
        Log.d("Debug", "Player Name: " + playerName + ", Total Score: " + totalScore);
    } else {
        lowestScoreTextView.setText("No data found");
    }

    cursor.close();
    db.close();
}

问题原因与修复方案 #

核心问题 #

totalScore字段被定义为TEXT类型，字符串排序逻辑和数值排序完全不同。例如字符串"10"会被判定为小于"9"，因为字符'1'的ASCII值小于'9'，导致查询结果错误。

修复步骤 #

1. 修改表结构，将totalScore改为数值类型
   修改DBHandler类中onCreate方法的CREATE TABLE语句，把DBTotal + " TEXT,"替换为：

   DBTotal + " INTEGER,"
   

2. 适配数据插入逻辑
   确保存入totalScore的是整数值：

   • 可以修改addNewPlayer方法的参数类型，将String CourseScore改为int CourseScore
   • 或者保留字符串参数，存入前转换为整数（需处理转换异常）：

     values.put(DBTotal, Integer.parseInt(CourseScore));
     

3. 触发数据库表更新
   将DBHandler中的版本号从ver = 1改为ver = 2，这样应用重启后会执行onUpgrade方法重建表（注意：此操作会清空现有数据，如需保留数据需额外编写数据迁移逻辑）

4. 优化查询语句（可选）
   使用MIN()函数直接定位最低分，查询更直观：

   SELECT name, totalScore FROM guestScores WHERE totalScore = (SELECT MIN(totalScore) FROM guestScores) LIMIT 1
   

内容的提问来源于stack exchange，提问作者Seals