java - SQLite 中不区分大小写的 GROUP BY 查询

Question

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

group by 之后的较低关键字没有任何区别。

"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";

我还尝试了 COLLATE 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

我解决了这个问题。 lower 关键字与 group by 配合使用效果很好。但我的错误是有些字段末尾有额外的空间。所以我使用 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";