Android Studio中Room框架SQLite CASE参数化多排序项问题

我已阅读相关技术文章，Code A在Android Studio中可正常运行。现在我需要实现：当排序参数sortBy为START_PRIORITY时，先按starred降序排序，再按createdDate降序排序。于是我把Code A改成了Code B，但Code B虽然能编译运行，排序结果却异常——所有情况都按Case WHEN :sortBy = 'TEXT_ASC' THEN text END asc的规则排序。另外Code C和Code D直接无法编译。

代码示例 #

Code A（正常运行） #

@Query("SELECT * FROM info_table ORDER BY " +
        "CASE WHEN :sortBy = 'START_PRIORITY' THEN starred END desc, " +         
        "Case WHEN :sortBy = 'TEXT_DESC' THEN text END desc, "+
        "Case WHEN :sortBy = 'TEXT_ASC' THEN text END asc"
)

Code B（可编译但结果异常） #

@Query("SELECT * FROM info_table ORDER BY " +
        "CASE WHEN :sortBy = 'START_PRIORITY' THEN starred END desc, createdDate desc," +         
        "Case WHEN :sortBy = 'TEXT_DESC' THEN text END desc, "+
        "Case WHEN :sortBy = 'TEXT_ASC' THEN text END asc"
)

Code C（无法编译） #

@Query("SELECT * FROM info_table ORDER BY " +
        "CASE WHEN :sortBy = 'START_PRIORITY' THEN starred ,createdDate END desc, " +         
        "Case WHEN :sortBy = 'TEXT_DESC' THEN text END desc, "+
        "Case WHEN :sortBy = 'TEXT_ASC' THEN text END asc"
)

Code D（无法编译） #

@Query("SELECT * FROM info_table ORDER BY " +
         "CASE WHEN :sortBy = 'START_PRIORITY' THEN starred END desc, THEN createdDate END desc," +
         "Case WHEN :sortBy = 'TEXT_DESC' THEN text END desc, "+
        "Case WHEN :sortBy = 'TEXT_ASC' THEN text END asc"
)

问题分析与解决方案 #

问题原因 #

1. Code B异常原因：当sortBy不是START_PRIORITY时，第一个CASE语句返回NULL，SQLite中NULL排序优先级极低，而后面的createdDate desc会一直参与排序，打乱了原本TEXT分支的排序逻辑，最终导致所有情况都走到TEXT_ASC的分支。
2. Code C编译失败原因：CASE语句的THEN后只能返回单个值，不能直接写两个字段（starred ,createdDate），语法不符合SQL规范。
3. Code D编译失败原因：语法结构混乱，多了一个多余的THEN关键字，导致SQL解析报错。

正确写法 #

要实现START_PRIORITY时的双重排序，同时不影响其他排序分支，需要给createdDate也加一层CASE判断，确保只有在sortBy为START_PRIORITY时才参与排序：

@Query("SELECT * FROM info_table ORDER BY " +
        "CASE WHEN :sortBy = 'START_PRIORITY' THEN starred END DESC, " +
        "CASE WHEN :sortBy = 'START_PRIORITY' THEN createdDate END DESC, " +
        "CASE WHEN :sortBy = 'TEXT_DESC' THEN text END DESC, " +
        "CASE WHEN :sortBy = 'TEXT_ASC' THEN text END ASC"
)

• 当sortBy为START_PRIORITY时，前两个CASE返回对应字段值，按降序排序，后两个CASE返回NULL，不参与排序；
• 当sortBy为TEXT_DESC或TEXT_ASC时，前两个CASE返回NULL，不参与排序，对应的TEXT分支生效。

内容的提问来源于stack exchange，提问作者HelloCW