Android / SQLite - 跨多个列的不同计数

I have the following code...

我有以下代码......

protected long getNumQueuedChunks(boolean distinctEntries) throws Exception {

SQLiteDatabase db = null;
try {

    db = dbhelper.getReadableDatabase();

    String whereClause = C_STATUS + " = ? AND " + C_NUM_FAILURES + " < ?";
    String[] whereArgs = new String[] {STATUS_AWAITING_PROCESSING, 10};

    long count = DatabaseUtils.queryNumEntries(db, QUEUE_TABLE_NAME, whereClause, whereArgs);           
    return count;
}
finally {
    try {
        db.close();
    }
    catch(Exception ignore) {
    }
}

}

...which works fine if I want to return the total amount of rows that match the WHERE condition.

...如果我想返回与WHERE条件匹配的行总数，它可以正常工作。

However, I would like to only count records that have distinct/unique combinations of data across these 3 columns: C_URI, C_BYTE_START_NUM and C_NUM_BYTES.

但是，我只想计算在这3列中具有不同/唯一数据组合的记录：C_URI，C_BYTE_START_NUM和C_NUM_BYTES。

I know I could do something like...

我知道我可以做点什么......

    String[] columns = {C_URI, C_BYTE_START_NUM, C_NUM_BYTES};
String whereClause = C_STATUS + " = ? AND " + C_NUM_FAILURES + " < ?";
String[] whereArgs = new String[] {STATUS_AWAITING_PROCESSING, "10"};
Cursor c = db.query(true, QUEUE_TABLE_NAME, columns, whereClause, whereArgs, null, null, null, null);
int count = c.getCount();

...but I am hoping there is a more efficient way to perform a distinct count in this situation??

...但我希望有更有效的方法在这种情况下执行一个独特的计数？

Just to add clarity, if I have this data in my table...

只是为了增加清晰度，如果我在表格中有这些数据......

C_URI | C_BYTE_START_NUM | C_NUM_BYTES

1.jpg | 0 | 1024

1.jpg | 1024 | 1999

1.jpg | 1024 | 1999年

2.jpg | 0 | 500

...the result of the distinct count should be 3.

......独特计数的结果应该是3。

NB - I have seen a similar requirement described here (second answer) but that doesn't help me as I am wanting to do a distinct count across 3 columns rather than just one.

注意 - 我已经看到了这里描述的类似要求（第二个答案），但这对我没有帮助，因为我想要对3列而不是仅仅一个进行明确的计数。

2 个解决方案

#1

The most efficient way of counting records is to let the database do this:

计算记录的最有效方法是让数据库执行此操作：

SELECT COUNT(*)
FROM (SELECT DISTINCT Uri,
                      ByteStartNum,
                      NumBytes
      FROM QueueTable)

(With the separate subquery, it does not matter if you use DISTINCT or GROUP BY over the three columns.)

（使用单独的子查询，如果在三列上使用DISTINCT或GROUP BY，则无关紧要。）

This query does not fit into the constraints of one of the helper functions like query or queryNumEntries, so you have to construct the entire SQL statement by hand:

此查询不适合查询或queryNumEntries之类的辅助函数之一的约束，因此您必须手动构造整个SQL语句：

long getNumQueuedChunks() {
    SQLiteDatabase db = dbhelper.getReadableDatabase();
    try {
        String query = "SELECT COUNT(*) FROM " +
                        "(SELECT DISTINCT " + C_URI + "," + C_BYTE_START_NUM + "," + C_NUM_BYTES +
                        " FROM " + QUEUE_TABLE_NAME +
                        " WHERE " + C_STATUS + " = ?" +
                        " AND " + C_NUM_FAILURES + " < 10)";
        String[] args = new String[] { STATUS_AWAITING_PROCESSING };
        return DatabaseUtils.longForQuery(db, query, args);
    } finally {
        db.close();
    }
}

#2

Have you tried the SQLiteDatabase.rawQuery() method? You can put a raw SQL query in it, for example, something like:

您是否尝试过SQLiteDatabase.rawQuery（）方法？您可以在其中放置原始SQL查询，例如：

select distinct C_URI, C_BYTE_START_NUM, C_NUM_BYTES from MyTable

The method returns a Cursor, and you can immediately get the count from the cursor object. Of course, you can specify a where clause if you want to as well. Then free up the Cursor once you got your count.

该方法返回一个Cursor，您可以立即从游标对象获取计数。当然，如果您愿意，也可以指定where子句。一旦计数完毕，就可以释放Cursor。

#1