游标返回错误的值 - sqlite - Android [英] Cursor returns wrong values - sqlite - Android

问题描述

我正在开发一个Sms应用程序，
我试图从每个会话中获取最后一个短信。

I'm developping an Sms Application, I'm trying to get the last sms from each conversation.

这里是我的SQL语句：

here's my SQL Statement :

SELECT MAX（smsTIMESTAMP）AS smsTIMESTAMP，_id，smsID，smsCONID，smsMSG，smsNUM，smsREAD，smsTYPE，smsSHORTMSG，COUNT（*）AS smsNUMMESSAGES FROM sms GROUP BY smsCONID ORDER BY smsTIMESTAMP desc

我在SQLite Expert中运行查询，我得到了正确的响应：

I ran the query in SQLite Expert and I get the right response :

>

但是，当我在我的应用程序中运行它时，我会得到：

however when I run it in my app I get :

这是我的表格

这里是我的Datamanipulator类：

Here's My Datamanipulator Class :

public class DataManipulator {
    private static final  String DATABASE_NAME = "smsapplication.db";
    private static final int DATABASE_VERSION = 3;
    static final String TABLE_NAME = "sms";
    private static Context context;
    static SQLiteDatabase db;
    private static DataManipulator instance;

    private SQLiteStatement insertStmt;

    private static final String INSERT = "insert or ignore into "
        + TABLE_NAME + " (smsID, smsCONID, smsMSG, smsNUM, smsREAD, smsTIMESTAMP, smsTYPE, smsSHORTMSG) values (?,?,?,?,?,?,?,?)";

    public DataManipulator(Context context) {
        DataManipulator.context = context;
        OpenHelper openHelper = new OpenHelper(DataManipulator.context);
        DataManipulator.db = openHelper.getWritableDatabase();
        this.insertStmt = DataManipulator.db.compileStatement(INSERT);
    }
    public static DataManipulator getInstance(Context mContext)
    {
        if(instance == null)
        {
            instance = new DataManipulator(mContext);
        }
        return instance;
    }
    public long insert(int smsID, int smsCONID, String smsMSG,
            String smsNUM, int smsREAD, long smsTIMESTAMP, String smsTYPE, String smsSHORTMSG) {
        this.insertStmt.bindLong(1, smsID);
        this.insertStmt.bindLong(2, smsCONID);
        this.insertStmt.bindString(3, smsMSG);
        this.insertStmt.bindString(4, smsNUM);
        this.insertStmt.bindLong(5, smsREAD);
        this.insertStmt.bindString(6, String.valueOf(smsTIMESTAMP));
        this.insertStmt.bindString(7, smsTYPE);
        this.insertStmt.bindString(8, smsSHORTMSG);
        return this.insertStmt.executeInsert();
    }

    public void deleteAll() {
        db.delete(TABLE_NAME, null, null);
    }

    public Cursor getLastSmsForAllConversations()
    {
        Cursor cursor = db.query(TABLE_NAME, new String[] {"_id"," MAX(smsTIMESTAMP) AS smsTIMESTAMP ", "smsCONID", "smsNUM", "smsREAD", "smsTYPE","smsSHORTMSG","COUNT(*) AS smsNUMMESSAGES"  },
                null, null, "smsCONID", null, "smsTIMESTAMP desc"); 
        return cursor;
    }
    public Cursor getConversationMessages(int conID)
    {
        Cursor cursor = db.query(TABLE_NAME, new String[] {"_id", "smsID", "smsCONID", "smsMSG", "smsNUM", "smsREAD", "smsTIMESTAMP", "smsTYPE","smsSHORTMSG"  },
                "smsCONID="+conID, null, null, null, "smsTIMESTAMP asc"); 
        return cursor;
    }
    public void printCursor()
    {
        Cursor cursor = db.rawQuery("SELECT MAX(smsTIMESTAMP) AS smsTIMESTAMP,_id, smsID, smsCONID, smsMSG, smsNUM, smsREAD, smsTYPE, smsSHORTMSG, COUNT(*) AS smsNUMMESSAGES FROM sms GROUP BY smsCONID ORDER BY smsTIMESTAMP desc", null);

            //  db.query(TABLE_NAME, new String[] {"_id","smsTIMESTAMP AS smsTIMESTAMP ", "smsCONID", "smsNUM", "smsREAD", "smsTYPE","smsSHORTMSG"  },"smsCONID=40", null, null, null, "smsTIMESTAMP desc");  
        if (cursor.moveToFirst()){
               do{
                  String data ="";
                  data= cursor.getString(cursor.getColumnIndex("smsTIMESTAMP"))+" - " +cursor.getString(cursor.getColumnIndex("smsSHORTMSG"));
                  Log.i("data", data);
               }while(cursor.moveToNext()); 
            }
            cursor.close();
    }

    private static class OpenHelper extends SQLiteOpenHelper {

        OpenHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL("CREATE TABLE " + TABLE_NAME + " (_id INTEGER , smsID INTEGER PRIMARY KEY, smsCONID INTEGER, smsMSG TEXT,smsNUM TEXT, smsREAD INTEGER, smsTIMESTAMP INTEGER, smsTYPE TEXT, smsSHORTMSG TEXT)");
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
            onCreate(db);
        }
         @Override
            public synchronized void close() {

            if (db != null)
                db.close();

            super.close();

            }
    } 
}

推荐答案

当您使用 GROUP BY 时，结果的每一行都对应于原始表的多行。
有三种可能性如何计算这些结果：

When you are using GROUP BY, each row of the result corresponds to multiple rows of the original table. There are three possibilities how these results are computed:

• 具有 MAX 或 COUNT 计算组中所有行的值;
  
• $ c> GROUP BY 子句只是取自群组中的任何记录（因为群组中的所有记录都具有相同的值）;


• 其他列是一个问题，因为组中的行中可能有不同的值。
  在标准SQL中，这些列是禁止的; SQLite允许它们，但只是给你从组中的任何随机行的值，这几乎从来没有你想要的。
  从SQLite 3.7.11开始，您从匹配 MIN 或 MAX 的行获取值;这是您在SQLite Exprert中看到的，但不是在具有旧SQLite的Android上。

• Columns with an aggregate function like MAX or COUNT compute the values from all rows in the group;
• columns that appear in the GROUP BY clause are just taken from any record in the group (because all records in the group have the same value for that column);
• other columns are a problem, because the rows in the group might have different values in them. In standard SQL, such columns are forbidden; SQLite allows them, but just gives you values from any random row in the group, which is almost never what you want. Beginning with SQLite 3.7.11, you get values from the row that matches a MIN or MAX; this is what you see in SQLite Exprert, but not on Androids with an older SQLite.

要解决您的问题，您必须首先使用 GROUP BY 获取足够的信息来标识所需的记录：

To solve your problem, you have to first use a GROUP BY to get enough information to identify the records you want:

SELECT smsCONID,                            -- OK: used in GROUP BY
       MAX(smsTIMESTAMP) AS smsTIMESTAMP,   -- OK: aggregate MAX
       COUNT(*) AS smsNUMMESSAGES           -- OK: aggregate COUNT
FROM sms
GROUP BY smsCONID
ORDER BY smsTIMESTAMP DESC

然后，将结果表与原始 sms 表结合，以获取这些记录的其他列：

Then, join that result table with the original sms table to get the other columns of these records:

SELECT *
FROM (SELECT smsCONID,
             MAX(smsTIMESTAMP) AS smsTIMESTAMP,
             COUNT(*) AS smsNUMMESSAGES
      FROM sms
      GROUP BY smsCONID) AS grouped
     JOIN sms
       ON grouped.smsCONID     = sms.smsCONID
      AND grouped.smsTIMESTAMP = sms.smsTIMESTAMP
ORDER BY smsTIMESTAMP DESC

这篇关于游标返回错误的值 - sqlite - Android的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！