Android SQLite的ORDER BY不起作用

我有一个非常基本的情况:

我有一个大约5k行的桌子:

CREATE TABLE "words" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "name" TEXT NOT NULL , "def" TEXT NOT NULL, "rand" INTEGER) 

我定期更新使用“更新单词SET rand = random()”

android中,当我使用下面的rawQuery()创build一个游标

 SELECT w.id, w.name, w.def, w.rand FROM words w ORDER BY w.rand ASC; 

返回的游标不会以正确的顺序迭代。 例如,它将按照以下顺序输出rand值的列:

 -1298882092 -2138143484 -1115732861 118839193 ... 

有谁知道这是怎么回事? 不应该这个工作? 如果我在SQLiteManager中运行完全相同的查询,它将以正确的顺序返回结果,所以这似乎是特定于android / cursor的。

更新:

这里是Android中的代码,我已经尝试了多种方式:

尝试1:

 Cursor cursor = db.rawQuery("SELECT w.id, w.name, w.def, w.rand FROM words w ORDER BY w.rand ASC", new String[]{}); 

尝试2:

 Cursor cursor = db.query("words", new String[]{"id", "name", "def", "rand"}, null, null, null, null, "rand ASC"); 

在这两种情况下,我重复如下:

 while(cursor.moveToNext()) { ... Log.i("Test", cursor.getInt(3)); ... } 

Solutions Collecting From Web of "Android SQLite的ORDER BY不起作用"

我解决了,是一个简单的疏忽。 random()函数返回的值可以大于java int数据types。 这是产生溢出。 切换到getLong(),一切正常。 游标始终正确迭代。

我有一个问题与Android的光标,原始查询和分组。 结果是没有命令。

这是表格:

 public static final String I_ID = "iid"; public static final String I_QID = "qid"; public static final String I_STATUS = "status"; public static final String I_PDATE = "pub_date"; public static final String I_TYPE = "tipo"; public static final String I_TITLE = "titolo"; public static final String I_LINK = "link"; public static final String I_QR = "qrcode"; public static final String I_DESC = "descrizione"; private static final String C_ITEM_T = "CREATE TABLE \""+T_ITEM+"\" ("+ "\""+I_ID+"\" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"+ "\""+I_QID+"\" INTEGER NOT NULL REFERENCES "+T_QUERY+"("+Q_ID+") ,"+ "\""+I_STATUS+"\" INTEGER NOT NULL,"+ "\""+I_PDATE+"\" TEXT NOT NULL,"+ "\""+I_TYPE+"\" TEXT NOT NULL,"+ "\""+I_TITLE+"\" TEXT NOT NULL,"+ "\""+I_LINK+"\" TEXT UNIQUE NOT NULL,"+ "\""+I_QR+"\" TEXT UNIQUE NOT NULL,"+ "\""+I_DESC+"\" TEXT"+ ");"; public static final String A_ID = "aid"; public static final String A_IID = "iid"; public static final String A_SIZE = "grandezza"; public static final String A_DURATION = "durata"; public static final String A_CHANNELS = "canali"; public static final String A_RATE = "sampleRate"; public static final String A_TORRENT = "torrent"; public static final String A_DOWNLOAD = "download"; public static final String A_LICENSE_URL = "licenza_url"; public static final String A_TAGS = "tags"; public static final String A_CREATOR = "creatore"; private static final String C_ATTR_T = "CREATE TABLE \""+T_ATTR+"\" ("+ "\""+A_ID+"\" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"+ "\""+A_IID+"\" INTEGER NOT NULL REFERENCES "+T_ITEM+"("+I_ID+") ,"+ "\""+A_SIZE+"\" LONG NOT NULL,"+ "\""+A_DURATION+"\" LONG NOT NULL,"+ "\""+A_CHANNELS+"\" TEXT NOT NULL,"+ "\""+A_RATE+"\" TEXT NOT NULL,"+ "\""+A_TORRENT+"\" TEXT NOT NULL,"+ "\""+A_DOWNLOAD+"\" TEXT NOT NULL,"+ "\""+A_LICENSE_URL+"\"TEXT,"+ "\""+A_TAGS+"\" TEXT,"+ "\""+A_CREATOR+"\" TEXT,"+ "UNIQUE ("+A_ID+","+A_IID+")"+ ");"; 

这是我的查询:

 public static final String FILL_GRID = "SELECT * "+ "FROM "+ Db.T_ITEM + " i " + "LEFT OUTER JOIN "+Db.T_ATTR+" a ON ( i."+Db.I_ID+"=a."+Db.A_IID+ ")"+ " WHERE i."+Db.I_QID+" = ? AND i."+Db.I_STATUS+" <> "+Db.STATUS_DELETED + " GROUP BY ? "; 

在这里如何使用它:

 Cursor c = db.rawQuery(FILL_GRID, new String[]{String.valueOf(qid), order}); 

我确信qid是一个整数,并命令i.titolo ASC或DESC

我唯一的解决方法是修改查询在rawquery调用方法中通过clausole移动组:

 public static final String FILL_GRID = "SELECT * "+ "FROM "+ Db.T_ITEM + " i " + "LEFT OUTER JOIN "+Db.T_ATTR+" a ON ( i."+Db.I_ID+"=a."+Db.A_IID+ ")"+ " WHERE i."+Db.I_QID+" = ? AND i."+Db.I_STATUS+" <> "+Db.STATUS_DELETED; Cursor c = db.rawQuery(FILL_GRID + " ORDER BY " + order, new String[]{String.valueOf(qid)});