我每次都会得到Sqlite中的随机重复数据，我想要的是不重复的随机数据

Question

我得到了很多时间重复数据从Sqlite / Android数据库。我希望在Android游标中不重复地重新编码。

以下是我的查询：

public Cursor getQuizQuiestion(String cat, String level, String questionNo) {
    String QUERY_SELECT_QUIESTION = "SELECT * FROM " +TABLE_QUIESTION +" WHERE "+COL_CAT+  " = '" +cat+"' AND "
            +COL_LEVEL+ " = " +level+" ORDER BY RANDOM() LIMIT 1";

    Cursor cursor = db.rawQuery(QUERY_SELECT_QUIESTION, null);

    return cursor;
}

Answer 1

--我有一个简单的例子--如果我在我的项目中使用--我认为MayBe是帮助您的；

 public ArrayList<ModelRandomList>  getRandomData(String city){

    ArrayList<ModelRandomList> modelRandomListArrayListLists = new ArrayList<ModelRandomList>();


    String queryRandomData ="SELECT  DISTINCT * FROM "+TABLE_NAME+" WHERE "+COL_CITY + "=?"+ "Order BY RANDOM()";

    Cursor cursor = db.rawQuery(queryRandomData,new String[] { String.valueOf(city) });

    if(cursor.getCount() != 0){

        while (cursor.moveToNext()){
            /*mName =cursor.getString(0);
            mCity = cursor.getString(1);

            Log.d(TAG,mName +" City "+mCity );*/


            ModelRandomList modelRandomList = new ModelRandomList();
            modelRandomList.setUserName(cursor.getString(0));
            modelRandomList.setUserCity(cursor.getString(1));

             modelRandomListArrayListLists.add(modelRandomList);
        }

        Random random = new Random();

        Collections.shuffle(modelRandomListArrayListLists,random);
    }


    Log.d(TAG, "Get Random Totla No of Recode Found "+cursor.getCount());



    return modelRandomListArrayListLists;
}

在你的活动中

public class MainActivity extends AppCompatActivity {
private static final String TAG = MainActivity.class.getSimpleName();
private SqlLiteDataBaseHelper sqlLiteDataBaseHelper;
private Button btGetRandomData;
private ArrayList<ModelRandomList> modelRandomLists;
private Button btSingleRandomData;
private int position = 0;
@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);

    modelRandomLists = new ArrayList<ModelRandomList>();

    sqlLiteDataBaseHelper = new SqlLiteDataBaseHelper(MainActivity.this);

    try{

        if(sqlLiteDataBaseHelper.checkDataBase()){

            Log.e(TAG, "Data Base Already Exists");

        }else {

            sqlLiteDataBaseHelper.CopyDataBaseFromAsset();
        }

        sqlLiteDataBaseHelper.openDataBase();

        try {
            Log.e(TAG, "No Of Racode In DataBase " + sqlLiteDataBaseHelper.getDataCount());
        }catch (Exception e){
            e.printStackTrace();
        }

    }catch (Exception e){
        e.printStackTrace();
    }


    init();
}

private void init() {

    btGetRandomData = (Button)findViewById(R.id.btRandomData);
    btSingleRandomData = (Button)findViewById(R.id.btSingleRandomData);
}

@Override
protected void onResume() {
    super.onResume();

    btGetRandomData.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View view) {

            modelRandomLists = sqlLiteDataBaseHelper.getRandomData("A");

           for (ModelRandomList crt : modelRandomLists) {

                Log.e(TAG, " " + crt.getUserName());
                Log.e(TAG, " " + crt.getUserCity());

            }
        }
    });


    btSingleRandomData.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View view) {


            try{
                Log.d(TAG,modelRandomLists.get(position).getUserName());
                position ++;

            }catch (Exception e){
                e.printStackTrace();
                position =0;
            }


        }
    });
}

}

Answer 2

如果您一遍又一遍地查询，就无法创建唯一的结果。

既然您不使用questionNo参数并返回游标:为什么不直接删除限制1并将不同的子句添加到语句中呢？

如果这样做，您可以使用游标来迭代唯一的问题：

String QUERY_SELECT_QUIESTION = "SELECT DISTINCT * FROM " +TABLE_QUIESTION +" WHERE "+COL_CAT+  " = '" +cat+"' AND "
        +COL_LEVEL+ " = " +level+" ORDER BY RANDOM()";

然后，您可以迭代这些问题，直到您没有或任何您的条件是：

Cursor cursor = db.rawQuery(QUERY_SELECT_QUIESTION, null);
try {
    while (cursor.moveToNext()) {
        //display question or copy them to a member or whatever
    }
} finally {
    cursor.close();
}

Answer 3

你必须跟踪之前看到过的问题。那这个呢？

private List<Integer> seenQuestions = new ArrayList<>();

public Cursor getUseenQuizQuestion(String cat,String level,String questionNo) {

   Cursor cursor = getQuizQuiestion(cat, level, questionNo);
   while(cursor == null) { // This could be an infinite loop!!
       cursor = getQuizQuiestion(cat, level, questionNo); 
   } 
   return cursor;
}

private Cursor getQuizQuiestion(String cat,String level,String questionNo){
    String QUERY_SELECT_QUIESTION = "SELECT * FROM " +TABLE_QUIESTION +" WHERE "+COL_CAT+  " = '" +cat+"' AND "
            +COL_LEVEL+ " = " +level+" ORDER BY RANDOM() LIMIT 1";
    Cursor cursor = db.rawQuery(QUERY_SELECT_QUIESTION, null);
    if(cursor.moveToFirst()) {
       int resultId = cursor.getString(cursor.getColumnIndexOrThrow("_id"));
        if(seenQuestions.contains(resultId)) {
             cursor.close();
             return null;
        }

       seenQuestions.add(resultId);
    }
    return cursor;
}

上面的代码示例有很多缺陷，可以无限循环。

但问题是，您需要跟踪返回的内容，并再次查询是否已经看到它。

或者，您可以允许DB查询返回所有数据，然后使用一个随机值来选择其中一个项。