java - Android - SQLite 如何按两列获取重复项？

Question

我有一个问题 - 由于以下行，我无法在我的数据库中应用此代码:

card = player_card.card

这一行在请求中非常重要。它在 Access 和其他系统中运行良好，但在 Android 中，它不起作用。应用程序不响应，甚至不产生错误。

你有什么建议？提前致谢。

SELECT * FROM player_card
WHERE email 
In (SELECT email FROM player_card As Tmp
   GROUP BY email, card 
   HAVING COUNT(*) > 1 AND card = player_card.card)
   ORDER BY email;

这是 SQL Fiddle

---

根据 MikeT 的回答进行编辑

非常感谢!你真的帮我走出了无限循环(for(;;))。当我将您的代码放入我的应用程序时...结果是相同的 - 应用程序无法运行。但为什么？ ...此 SQL 代码不适用于 Android 上的大型数据库 - 我当前的数据库包含 12k 行和 17 列。移动硬件太弱，无法执行此类任务。当我将数据库减少到 3 k 时——代码运行大约 1 分钟。最后，我明白了两件事:

类似的代码

card = player_card.card

不能用于大型 Android 数据库；

Answer 1

你一定是其他地方出了问题，代码在 android 上工作正常，按照(sql 复制到 logIt 方法):-

public class DBHelper001 extends SQLiteOpenHelper {

    public static final String DBNAME = "db";
    public static final int DBVERSION =1;

    public static final String TABLE_PLAYER = "player_card";
    public static final String COL_PLAYERCARD_CARD = "card";
    public static final String COL_PLAYERCARD_EMAIL = "email";
    public static final String COL_PLAYERCARD_REGION = "region";
    public static final String COL_PLAYERCARD_Q = "quantidade";

    SQLiteDatabase mDB;

    public DBHelper001(Context context) {
        super(context, DBNAME, null, DBVERSION);
        mDB = this.getWritableDatabase(); //<<<<<<<<<< when helper is instantiated will force on Create and thus the logIt method to run.
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        mDB = db;
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_PLAYER + "(" +
                COL_PLAYERCARD_CARD + " TEXT, " +
                COL_PLAYERCARD_EMAIL + " TEXT, " +
                COL_PLAYERCARD_REGION + " TEXT," +
                COL_PLAYERCARD_Q + " INTEGER," +
                "PRIMARY KEY (" +
                COL_PLAYERCARD_EMAIL + "," +
                COL_PLAYERCARD_REGION + "," +
                COL_PLAYERCARD_CARD + ")" +
                ")"
        );
        //<<<<<<<<<< Add some test data
        insertPlayerCard("card1","a@x.id","EU",1);
        insertPlayerCard("card2","a@x.id","EU",1);
        insertPlayerCard("card1","a@x.id","US",1);
        //<<<<<<<<<< INVOKE THE LOGIT METHOD >>>>>>>>>>
        logIt();
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i1) {

    }

    public long insertPlayerCard(String card, String email, String region, long quantitade) {
        ContentValues cv = new ContentValues();
        cv.put(COL_PLAYERCARD_CARD,card);
        cv.put(COL_PLAYERCARD_EMAIL,email);
        cv.put(COL_PLAYERCARD_REGION,region);
        cv.put(COL_PLAYERCARD_Q,quantitade);
        return mDB.insert(TABLE_PLAYER,null,cv);
    }

    //<<<<<<<<<< THE LOGIT METHOD RUNS QUERY AS COPIED AND REPORTS ROWS EXTRACTED >>>>>>>>>>
    public void logIt() {
        String sql = "SELECT * FROM player_card " +
                "WHERE email " +
                "In (SELECT email FROM player_card As Tmp " +
                "   GROUP BY email, card " +
                "   HAVING COUNT(*) > 1 AND card = player_card.card) " +
                "   ORDER BY email;";
        Cursor csr = mDB.rawQuery(sql,null);
        Log.d("RESULT","Number of rows extracted = " + String.valueOf(csr.getCount()));
        csr.close();
    }
}

简单实例化和实例(如果不存在数据库)运行 logIt 方法，返回:-

12-08 05:31:50.247 2049-2049/? D/RESULT: Number of rows extracted = 2

---

对原始问题的额外重新编辑

并不是说你不能在 Android 上做到这一点，你可以，但是这需要时间。

简而言之，上面的数据库加载了 20997 行并且查询应用了 3 次:-

• 第一个补偿缓存/初始运行问题
• 第二次是第一次试运行。
• 第 3 次在卡片列上创建索引后。

每次运行都有效，但每次都需要 20 分钟(在模拟设备上，我相信这可能比某些真实设备更快)。

• 注意所有在一个线程中运行以避免 ANR(Android 没有响应，也许这是你的问题)

结果记录为:-

12-09 02:02:33.325 3702-3715/so53646027.so53646027 D/THREADREPORT: Started at (getting row count)2018-12-09 00:57:35.609+0000
        Number of Rows in player_card is 20997 at 2018-12-09 00:57:35.633+0000
        Finished Run to ignore chacheing effects at 2018-12-09 01:19:04.769+0000
        Finished Test Run without Index (started creating index on card column) at 2018-12-09 01:40:54.147+0000
        Finished Creating Index at; Starting Test Run with Index at 2018-12-09 01:40:54.223+0000
        Finished 2nd Test Run (now dropping index) at 2018-12-09 02:02:33.321+0000
        Finished at 2018-12-09 02:02:33.328+0000

然后我复制了数据库并在 Navicat(这里不是地球上最慢的 PC)中运行了完全相同的查询(一次)，运行时间不到 6 分钟。

从其他测试来看，card = player_card.card 显然是核心问题。将其移动为一个 WHERE 子句，可以显着减少查询时间，我认为这是一个可管理/可接受的时间，例如:-

12-09 06:09:54.501 4845-4858/so53646027.so53646027 D/THREADREPORT: Started at (getting row count)2018-12-09 06:09:50.474+0000
        Number of Rows in player_card is 20997 at 2018-12-09 06:09:50.477+0000
        Finished Run to ignore chacheing effects at 2018-12-09 06:09:51.858+0000
        Finished Test Run without Index (started creating index on card column) at 2018-12-09 06:09:52.969+0000
        Finished Creating Index at; Starting Test Run with Index at 2018-12-09 06:09:53.048+0000
        Finished 2nd Test Run (now dropping index) at 2018-12-09 06:09:54.499+0000
        Finished at 2018-12-09 06:09:54.505+0000

大约 1.5 秒，与完整日志中报告的结果相同:-

12-09 06:09:51.853 4845-4858/so53646027.so53646027 D/RESULT: Number of rows extracted = 7368
12-09 06:09:51.853 4845-4858/so53646027.so53646027 D/THREADREPORT: Finished Run to ignore cacheing at 2018-12-09 06:09:51.857+0000
12-09 06:09:52.965 4845-4858/so53646027.so53646027 D/RESULT: Number of rows extracted = 7368
12-09 06:09:52.965 4845-4858/so53646027.so53646027 D/THREADREPORT: Finished 1st Test RUN (not indexed, now building Index) at 2018-12-09 06:09:52.969+0000
12-09 06:09:53.045 4845-4858/so53646027.so53646027 D/THREADREPORT: Index created (starting 2nd Test Run) at 2018-12-09 06:09:53.048+0000
12-09 06:09:54.493 4845-4858/so53646027.so53646027 D/RESULT: Number of rows extracted = 7368
12-09 06:09:54.493 4845-4858/so53646027.so53646027 D/THREADREPORT: Finished 2nd Test RUN (dropping Index) at 2018-12-09 06:09:54.499+0000
12-09 06:09:54.501 4845-4858/so53646027.so53646027 D/THREADREPORT: Finished at 2018-12-09 06:09:54.505+0000
12-09 06:09:54.501 4845-4858/so53646027.so53646027 D/THREADREPORT: Started at (getting row count)2018-12-09 06:09:50.474+0000
        Number of Rows in player_card is 20997 at 2018-12-09 06:09:50.477+0000
        Finished Run to ignore chacheing effects at 2018-12-09 06:09:51.858+0000
        Finished Test Run without Index (started creating index on card column) at 2018-12-09 06:09:52.969+0000
        Finished Creating Index at; Starting Test Run with Index at 2018-12-09 06:09:53.048+0000
        Finished 2nd Test Run (now dropping index) at 2018-12-09 06:09:54.499+0000
        Finished at 2018-12-09 06:09:54.505+0000

即自始至终，我一直返回 7368 行。

因此我相信您可以使用/转换:-

public void logIt() {
    String sql = "SELECT * FROM player_card " +
            "WHERE email " +
            "In (SELECT email FROM player_card As Tmp " +
            "   WHERE card = player_card.card " +
            "   GROUP BY email, card " +
            "   HAVING COUNT(*) > 1) " +
            "   ORDER BY email;";
    Cursor csr = mDB.rawQuery(sql,null);
    Log.d("RESULT","Number of rows extracted = " + String.valueOf(csr.getCount()));
    csr.close();