Android：ContentResolver 中的 Distinct 和 GroupBy
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2315203/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Android: Distinct and GroupBy in ContentResolver
提问by Bostone
What would be the correct way to add DISTINCTand/or GROUPBYto ContentResolver-based queries?
什么是添加正确的方式DISTINCT和/或GROUPBY以ContentResolver基于查询？
Right now I have to create custom URI for each special case.
现在我必须为每个特殊情况创建自定义 URI。
Is there a better way?
有没有更好的办法？
(I still program for 1.5 as lowest common denominator)
（我仍然编程为 1.5 作为最低公分母）
采纳答案by Bostone
Since no one came to answer I'm just going to tell how I solved this. Basically I would create custom URI for each case and pass the criteria in selectionparameter. Then inside ContentProvider#queryI would identify the case and construct raw query based on table name and selection parameter.
由于没有人来回答我只是要告诉我是如何解决这个问题的。基本上我会为每种情况创建自定义 URI 并在selection参数中传递条件。然后在内部ContentProvider#query我将识别案例并根据表名和选择参数构造原始查询。
Here's quick example:
这是一个快速示例：
switch (URI_MATCHER.match(uri)) {
case TYPES:
table = TYPES_TABLE;
break;
case TYPES_DISTINCT:
return db.rawQuery("SELECT DISTINCT type FROM types", null);
default:
throw new IllegalArgumentException("Unknown URI " + uri);
}
return db.query(table, null, selection, selectionArgs, null, null, null);
回答by kzotin
You can do nice hack when querying contentResolver, use:
您可以在查询 contentResolver 时进行很好的破解，请使用：
String selection = Models.SOMETHING + "=" + something + ") GROUP BY (" + Models.TYPE;
回答by P-A
If you want to use DISTINCT with SELECT more then one column, You need to use GROUP BY.
Mini Hack over ContentResolver.query for use this:
如果要将 DISTINCT 与 SELECT 一起使用超过一列，则需要使用 GROUP BY。
Mini Hack over ContentResolver.query 使用这个：
Uri uri = Uri.parse("content://sms/inbox");
Cursor c = getContentResolver().query(uri,
new String[]{"DISTINCT address","body"}, //DISTINCT
"address IS NOT NULL) GROUP BY (address", //GROUP BY
null, null);
if(c.moveToFirst()){
do{
Log.v("from", "\""+c.getString(c.getColumnIndex("address"))+"\"");
Log.v("text", "\""+c.getString(c.getColumnIndex("body"))+"\"");
} while(c.moveToNext());
}
This code select one last sms for each of senders from device inbox.
Note: before GROUP BY we always need to write at least one condition.
Result SQL query string inside ContentResolver.query method will:
此代码从设备收件箱中为每个发件人选择最后一条短信。
注意：在 GROUP BY 之前，我们总是需要至少写一个条件。ContentResolver.query 方法中的结果 SQL 查询字符串将：
SELECT DISTINCT address, body FROM sms WHERE (type=1) AND (address IS NOT NULL) GROUP BY (address)
回答by Brady Kroupa
In your overridden ContentProviderquery method have a specific URI mapping to using distinct.
在您重写的ContentProvider查询方法中，有一个特定的 URI 映射到使用 distinct。
Then use SQLiteQueryBuilderand call the setDistinct(boolean)method.
然后使用SQLiteQueryBuilder并调用该setDistinct(boolean)方法。
@Override
public Cursor query(Uri uri, String[] projection, String selection,
String[] selectionArgs, String sortOrder)
{
SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
boolean useDistinct = false;
switch (sUriMatcher.match(uri))
{
case YOUR_URI_DISTINCT:
useDistinct = true;
case YOUR_URI:
qb.setTables(YOUR_TABLE_NAME);
qb.setProjectionMap(sYourProjectionMap);
break;
default:
throw new IllegalArgumentException("Unknown URI " + uri);
}
// If no sort order is specified use the default
String orderBy;
if (TextUtils.isEmpty(sortOrder))
{
orderBy = DEFAULT_SORT_ORDER;
}
else
{
orderBy = sortOrder;
}
// Get the database and run the query
SQLiteDatabase db = mDBHelper.getReadableDatabase();
// THIS IS THE IMPORTANT PART!
qb.setDistinct(useDistinct);
Cursor c = qb.query(db, projection, selection, selectionArgs, null, null, orderBy);
if (c != null)
{
// Tell the cursor what uri to watch, so it knows when its source data changes
c.setNotificationUri(getContext().getContentResolver(), uri);
}
return c;
}
回答by W00di
Though I have not used Group By, I have used Distinctin content resolver query.
虽然我没有使用Group By，但我在内容解析器查询中使用了Distinct。
Cursor cursor = contentResolver
.query(YOUR_URI,
new String[] {"Distinct "+ YOUR_COLUMN_NAME},
null,
null, null);
Cursor cursor = contentResolver
.query(YOUR_URI,
new String[] {"Distinct "+ YOUR_COLUMN_NAME},
null,
null, null);
回答by TacoEater
Adding the Distinct keyword in the projection worked for me too, however, it only worked when the distinct keyword was the first argument:
在投影中添加 Distinct 关键字也对我有用，但是，它仅在 distinct 关键字是第一个参数时才起作用：
String[] projection = new String[]{"DISTINCT " + DBConstants.COLUMN_UUID, ... };
回答by KLM
When you have multiple columns in your projection you should do like this:
当您的投影中有多个列时，您应该这样做：
val uri = MediaStore.Images.Media.EXTERNAL_CONTENT_URI
val projection = arrayOf(
"DISTINCT " + MediaStore.Images.Media.BUCKET_ID,
MediaStore.Images.Media.BUCKET_DISPLAY_NAME,
MediaStore.Images.Media.BUCKET_ID,
MediaStore.MediaColumns.DATA
)
val groupBySelection = " 1) GROUP BY (${MediaStore.Images.Media.BUCKET_ID}"
contentResolver.query(
uri,
projection,
null,
groupBySelection,
null,
null
)
groupBySelection with closing bracket and number "1" inside is a tiny hack, but it works absolutely fine
groupBySelection 与右括号和数字“1”里面是一个小技巧，但它工作得非常好
回答by Khizhny Andrey
// getting sender list from messages into spinner View
Spinner phoneListView = (Spinner) findViewById(R.id.phone_list);
Uri uri = Uri.parse("content://sms/inbox");
Cursor c = getContentResolver().query(uri, new String[]{"Distinct address"}, null, null, null);
List <String> list;
list= new ArrayList<String>();
list.clear();
int msgCount=c.getCount();
if(c.moveToFirst()) {
for(int ii=0; ii < msgCount; ii++) {
list.add(c.getString(c.getColumnIndexOrThrow("address")).toString());
c.moveToNext();
}
}
phoneListView.setAdapter(new ArrayAdapter<String>(BankActivity.this, android.R.layout.simple_dropdown_item_1line, list));
回答by Mikonos
In some condition, we can use "distinct(COLUMN_NAME)" as the selection, and it work perfect. but in some condition, it will cause a exception.
在某些情况下，我们可以使用“distinct(COLUMN_NAME)”作为选择，而且效果很好。但在某些情况下，它会导致异常。
when it cause a exception, i will use a HashSet to store the column values....
当它导致异常时，我将使用 HashSet 来存储列值....
回答by javment
Maybe its more simple to get distinct values, try to add the DISTINCT word before the column name you want into the projection table
也许获取不同的值更简单，请尝试在您想要的列名之前添加 DISTINCT 词到投影表中
String[] projection = new String[]{
BaseColumns._ID,
"DISTINCT "+ Mediastore.anything.you.want
};
and use it as an argument to query method of the content resolver!
并将其用作内容解析器查询方法的参数！
I hope to help you, cause I have the same question before some days
我希望能帮到你，因为前几天我也有同样的问题
