如何按输入字母排序SQLite查询的结果

Question

String sql = "SELECT * FROM " + TABLE_NAME +
                " WHERE " + ENGLISH + " LIKE ? ORDER BY " + ENGLISH + " LIMIT 100";
try {
            cursor = db.rawQuery(sql, new String[]{"%" + englishWord + "%"});

例如，在ENGLISH列中，有"ice"、"dice"、"vice"等。当我在englishWord中输入"ice"这个词时，我将得到的结果首先是"dice"，其次是"ice"one_answers"vice"。但我希望"冰"在一阶上因为它有确切的词。我如何才能做到这一点?

Answer 1

使用LENGTH函数获得精确匹配的短语，这应该是您的查询

String sql = "SELECT * FROM " + TABLE_NAME +
                " WHERE " + ENGLISH + " LIKE ? ORDER BY LENGTH(" + ENGLISH + ") LIMIT 100";

Answer 2

如果要先排序精确匹配，则添加另一个order BY表达式，该表达式返回精确匹配的较小数字:

rawQuery("... ORDER BY "+ENGLISH+" != ?, "+ENGLISH+"...",
         new String[]{ "%"+englishWord+"%", englishWord });

English != ?为布尔表达式;它返回0或1