通过SQLite中选择参数顺序排序的查询结果 [英] Sorting query results by order of selection arguments in SQLite

问题描述

那么，我做的是：
有歌曲，在一个特定的顺序。
我存储的歌曲ID的列表，以便以后可以接他们，以相同的顺序。

So what I am doing is: There are songs, in a specific order. I am storing a list of IDs of the songs so that I can fetch them later, in the same order.

我存储的ID列表变量里面，这是怎么了获取它们：

I am storing the list of IDs inside the variable in, and this is how I am fetching them:

StringBuilder sb = new StringBuilder( in.length * 2 - 1 );
        sb.append( "?" );
        for ( int i = 1; i < in.length; i ++ )
        {
            sb.append( ",?" );
        }
        String strIn = sb.toString( );

songCursor = context.getContentResolver( ).query( MediaStore.Audio.Media.EXTERNAL_CONTENT_URI, TRACK_COLUMNS,
                    MediaStore.MediaColumns._ID + " IN (" + strIn + ")", in, null );

一切工作正常，唯一的问题是，名单是按不同的顺序不是储存。在这里，我不能使用的排序顺序，因为显然，我想没有指定的顺序。我想这是在它被存储顺序，但我不知道我该怎么做。

Everything is working fine, the only problem is, the list is in a different order than as was stored. Here I can't use sort order since obviously the order in which I want is not specified. I want it to be in the order in which it was stored but I have no idea how I can do it.

另外这是当鼠标悬停在查询给出的信息，这样解释了原因，但我没有一个解决方案。

Also this is the information given while hovering over the query, so that explains the reason, but I don't have a solution.

selectionArgs两个可在包括？的中选择，这将被替换
  从selectionArgs两个值中，使他们出现在
  选择。

selectionArgs You may include ?s in selection, which will be replaced by the values from selectionArgs, in the order that they appear in the selection.

例如：

在上面的图片，我存储的所有歌曲的ID在它们的顺序。

In the above picture, I am storing the ids of all the songs in the order in which they are.

但是，当我取回他们的顺序被改变。

But when I am retrieving them, the order is changed.

推荐答案

继的例子这个帖子中，$ C $下面C建立了一个 CASE 前pression在查询中将sortOrder 参数（ ）通话，旁边的选参数。

Following the example in this post, the code below builds a CASE expression for the sortOrder argument in the query() call, alongside the selection argument.

StringBuilder inStr = new StringBuilder(MediaStore.MediaColumns._ID)
    .append(" IN (?");

StringBuilder orderStr = new StringBuilder("CASE ")
    .append(MediaStore.MediaColumns._ID)
    .append(" WHEN ")
    .append(in[0])
    .append(" THEN 0");

for (int i = 1; i < in.length; i++) {
    inStr.append(",?");

    orderStr.append(" WHEN ")
        .append(in[i])
        .append(" THEN ")
        .append(i);
}

inStr.append(")");

orderStr.append(" END, ")
    .append(MediaStore.MediaColumns._ID)
    .append(" ASC");

Cursor songCursor = getContentResolver().query(MediaStore.Audio.Media.EXTERNAL_CONTENT_URI,
                                               TRACK_COLUMNS,
                                               inStr.toString(),
                                               in,
                                               orderStr.toString());

的 CASE 前pression实质上分配的别名值每个ID进行排序，而不是实际的ID值排序。无论第一个ID是在选择什么，它的别名值将是 0 ，第二个将是 1 ，第三 2 等排序方式递增这些值，由此产生的光标将在ID的传递顺序在选择。

The CASE expression is essentially assigning an alias value to each ID to sort by, rather than sorting by the actual ID value. No matter what the first ID is in the selection, its alias value will be 0, the second will be 1, the third 2, etc. Ordering by these values ascendingly, the resulting Cursor will be in the order of the IDs passed in the selection.

这篇关于通过SQLite中选择参数顺序排序的查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！