SQLite的获取只有最后一个记录不是所有的记录

Question

我也总4条记录ecare表，其中2 records状态发送= 1，和其他2条状态sent = 0

RECORDS

1st Record : Track 1 [sent = 1] 

2nd Record : Track 2 [sent = 1] 

3rd Record : Track 3 [sent = 0] 

4th Record : Track 4 [sent = 0] 

但只得到最后记录（即 - 轨道4）不全2记录那些状态发送= 0

这里是SQLite的查询，其中我使用来从数据库

public synchronized List<String> getECareData() { 

     String sql = "SELECT p.sent,e.*, e.no _id from ecare e LEFT JOIN pweb p ON e.h_id=p.h_id WHERE p.sent = '0' GROUP BY e.h_id"; 

     List<String> records = new ArrayList<String>(); 
     SQLiteDatabase db = this.getWritableDatabase(); 
     Cursor cursor = db.rawQuery(sql, null); 
     while (cursor.moveToNext()) { 
      records.add(cursor.getString(0)); 
     } 
     return records; 
    } 

数据进行更新

public synchronized List<String> getECareData() { 

    String sql = "SELECT p.sent,e.*, e.no _id from ecare e LEFT JOIN pweb p ON e.h_id=p.h_id WHERE p.sent = '0' GROUP BY e.h_id"; 

    List<String> records = new ArrayList<String>(); 
    SQLiteDatabase db = this.getWritableDatabase(); 
    Cursor cursor = db.rawQuery(sql, null); 

    // looping through all rows and adding to list 
    if (cursor.moveToFirst()) { 
     do { 
      records.add(cursor.getString(0)); 
     } while (cursor.moveToNext()); 
    } 

    Log.d("records:", records.toString()); 

    return records; 
} 

日志说：

D/records:: [Getting only LAST record here] the 4th one 

我想，有一个与查询东西wrong，因为我在只获得最后一个记录光标

更新的记录

1st Record : Track 1 [sent = 1] 

2nd Record : Track 2 [sent = 1] 

3rd Record : Track 3 [sent = 0] 

4th Record : Track 4 [sent = 0] 

5th Record : Track 5 [sent = 0] 

但只得到最后记录（即 - 轨道5）不所有三个记录那些状态发送= 0

日志说：

D/records:: [Getting only LAST record here] the 5th one 

更新的查询 - 从查询中移除GROUP BY

现在得到每一条记录，即使是那些属于的发送= 1而我只是想获取属于发送= 0的数据仅

String sql = "SELECT p.sent,e.*, e.no _id from ecare e LEFT JOIN pweb p ON e.h_id=p.h_id WHERE p.sent = '0' "; 

Answer 1

你缺少startManagingCursor()方法。

Cursor cursor = db.rawQuery(sql, null); 
    cursor.getCount(); 
    startManagingCursor(cursor);//the important line 
     cursor.moveToFirst(); 
     if(cursor.getCount() == 0) 
     { 
      //error no records 
     } 
     else 
     { 
     for(int i=0;i<cursor.getCount();i++) 
     { 
       records.add(cursor.getString(0)); 
      adapter.add(aString); 
      cursor.moveToNext(); 
     } 
     cursor.moveToFirst(); 
     } 

注： startManagingCursor()被推荐使用，因为它的主线程可以冻结用户界面，并提供一个不好的用户体验上的操作。您应该使用CursorLoader而不是LoaderManager。

Answer 2

我会尝试：

String sql = "SELECT p.sent,e.*, e.no _id 
       FROM ecare e, pweb p 
       WHERE e.h_id=p.h_id 
       AND e.sent = '0' "; 

Answer 3

我试图重新创建数据库，这是正确的，这是什么问题，似乎工作。

CREATE TABLE ecare (_id INTEGER PRIMARY KEY,h_id INTEGER ,no INTEGER); 
CREATE TABLE pweb (_id INTEGER PRIMARY KEY,h_id INTEGER ,sent INTEGER); 
sqlite> insert into ecare values(1, 10,1); 
sqlite> insert into ecare values(2, 20,2); 
sqlite> insert into ecare values(3, 30,3); 
sqlite> insert into ecare values(4, 40,4); 
sqlite> 
sqlite> insert into pweb values(1, 10,1); 
sqlite> insert into pweb values(2, 20,1); 
sqlite> insert into pweb values(3, 30,0); 
sqlite> insert into pweb values(4, 40,0); 
sqlite> select * from ecare; 
1|10|1 
2|20|2 
3|30|3 
4|40|4 
sqlite> select * from pweb; 
1|10|1 
2|20|1 
3|30|0 
4|40|0 
sqlite> SELECT p.sent,e.*, e.no _id from ecare e LEFT JOIN pweb p ON e.h_id=p.h_id WHERE p.sent = '0'; 
0|3|30|3|3 
0|4|40|4|4 
sqlite>