SQLite中不区分大小写的分组查询

Question

我试图进行查询，并按字段对结果进行不区分大小写的分组。这是我试过的，但不起作用

分组后的下一个关键字没有任何区别

"SELECT " + MediaStore.Audio.Media.ARTIST + ", count(LOWER(" + MediaStore.Audio.Media.ARTIST +
                    ")) FROM " + PlaylistDB.TABLE_ALL_TRACKS + " GROUP BY LOWER(" + MediaStore.Audio.Media.ARTIST
                    +") HAVING " + MediaStore.Audio.Media.ARTIST
                    + " LIKE ? ORDER BY "+ MediaStore.Audio.Media.ARTIST + " COLLATE NOCASE ASC";

我还试着整理一下NOCASE。也不起作用

"SELECT " + MediaStore.Audio.Media.ARTIST + ", count(LOWER(" + MediaStore.Audio.Media.ARTIST +
                ")) FROM " + PlaylistDB.TABLE_ALL_TRACKS + " GROUP BY LOWER(" + MediaStore.Audio.Media.ARTIST
                +") HAVING " + MediaStore.Audio.Media.ARTIST
                + " LIKE ? ORDER BY "+ MediaStore.Audio.Media.ARTIST + " COLLATE NOCASE ASC";

你有什么建议让我得到不区分大小写的分组吗

Answer 1

我解决了这个问题。较低的关键字适用于group by fine。但我的错误是，有些字段的末尾有额外的空间。所以我用trim关键字修复了它。下面的代码可以根据我的需要工作

"SELECT " + MediaStore.Audio.Media.ARTIST + ", count(LOWER(TRIM(" + MediaStore.Audio.Media.ARTIST +
                "))) FROM " + PlaylistDB.TABLE_ALL_TRACKS + " GROUP BY LOWER(TRIM(" + MediaStore.Audio.Media.ARTIST
                +")) HAVING " + MediaStore.Audio.Media.ARTIST
                + " LIKE ? ORDER BY "+ MediaStore.Audio.Media.ARTIST + " COLLATE NOCASE ASC";