如何在Android Studio中使用CASE为SQLite与Room请求参数时添加sencond排序项？

cl25kdpy 于 2023-05-21 发布在 Android

关注(0)|答案(1)|浏览(109)

我读了article
代码A可以在Android Studio中很好地工作。
目前，我希望先按starred desc对记录进行排序，然后再按createdDate desc进行排序，因此我将Code A替换为Code B。
代码B可以编译和运行，但我得到错误结果，所有情况都指向“Case WHEN :sortBy = 'TEXT_ASC' THEN text END asc"”。
顺便说一句，代码C和代码D无法编译。

代码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"
)

代码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"
)

代码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"
)

代码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"
)

android

来源：https://stackoverflow.com/questions/76278049/how-can-i-add-the-sencond-sort-item-when-i-use-case-to-require-parameter-for-sql

1条答案

按热度按时间

wqnecbli1#

我不相信你想要的是可行的使用CASE WHEN THEN ELSE END结构，因为它们只能用于表达式是可接受的。

您可能希望参考https://www.sqlite.org/lang_select.html
*注参见其他，因为使用CASE WHEN THEN ELSE END结构是可行的。然而，使用CTE可能更容易理解。

然而，由于您似乎认为其他选项，根据您问题中的链接，不可接受，那么第四种方法可能是利用公共表表达式（CTE）和/或子查询（前者通常更简单）。
此方法可能涉及3个底层查询（CTE），其中只有一个将根据WHERE子句返回任何行，相应地SELECT s和ORDER s。最后的输出是使用UNION s组合的3个查询。
考虑使用SQLite工具（Navicat）的这个示例/演示，注意它演示了所有3种排列：

/* Cleanup Testing Environment (just in case) */
DROP TABLE IF EXISTS info_table;
/* Create the table and then load some testing data */
CREATE TABLE IF NOT EXISTS info_table (id INTEGER PRIMARY KEY, `text` TEXT, createdDate TEXT, starred TEXT);
INSERT INTO info_table (createdDate, starred, `text`) VALUES
    ('2023-01-01','starred001','ZZZZ')
    ,('2023-01-01','starred006','AAAA')
    ,('2022-01-01','starred009','YYYY')
    ,('2022-02-01','starred008','XXXX')
    ,('2022-03-01','starred007','WWWW')
;
/* First run */
WITH 
    /* optional but allows testing outside of room  in sqlite tool*/
    cte1(sortBy) AS (SELECT 'START_PRIORITY' /*'TEXT_DESC'*/ /*'TEXT_ASC'*/),
     /* only 1 of the following will select any rows due to the WHERE clause*/
    cte2 AS (SELECT * FROM info_table WHERE (SELECT sortBy FROM cte1) = 'START_PRIORITY' ORDER BY createddate DESC, starred DESC),
    cte3 AS (SELECT * FROM info_table WHERE (SELECT sortBy FROM cte1) = 'TEXT_DESC' ORDER BY `TEXT` DESC),
    cte4 AS (SELECT * FROM info_table WHERE (SELECT sortBy FROM cte1) = 'TEXT_ASC' ORDER BY `TEXT` ASC)
 /* combine all three outputs 2 of which will return no rows */
SELECT * FROM cte2 UNION ALL SELECT * FROM cte3 UNION ALL SELECT * FROM cte4
;
/* Second run */
WITH 
    /* optional but allows testing outside of room  in sqlite tool*/
    cte1(sortBy) AS (SELECT /*'START_PRIORITY'*/ 'TEXT_DESC' /*'TEXT_ASC'*/),
     /* only 1 of the following will select any rows due to the WHERE clause*/
    cte2 AS (SELECT * FROM info_table WHERE (SELECT sortBy FROM cte1) = 'START_PRIORITY' ORDER BY createddate DESC, starred DESC),
    cte3 AS (SELECT * FROM info_table WHERE (SELECT sortBy FROM cte1) = 'TEXT_DESC' ORDER BY `TEXT` DESC),
    cte4 AS (SELECT * FROM info_table WHERE (SELECT sortBy FROM cte1) = 'TEXT_ASC' ORDER BY `TEXT` ASC)
 /* combine all three outputs 2 of which will return no rows */
SELECT * FROM cte2 UNION ALL SELECT * FROM cte3 UNION ALL SELECT * FROM cte4
;
/* Thrid and final run */
WITH 
    /* optional but allows testing outside of room  in sqlite tool*/
    cte1(sortBy) AS (SELECT /*'START_PRIORITY'*/ /*'TEXT_DESC'*/ 'TEXT_ASC'),
     /* only 1 of the following will select any rows due to the WHERE clause*/
    cte2 AS (SELECT * FROM info_table WHERE (SELECT sortBy FROM cte1) = 'START_PRIORITY' ORDER BY createddate DESC, starred DESC),
    cte3 AS (SELECT * FROM info_table WHERE (SELECT sortBy FROM cte1) = 'TEXT_DESC' ORDER BY `TEXT` DESC),
    cte4 AS (SELECT * FROM info_table WHERE (SELECT sortBy FROM cte1) = 'TEXT_ASC' ORDER BY `TEXT` ASC)
 /* combine all three outputs 2 of which will return no rows */
SELECT * FROM cte2 UNION ALL SELECT * FROM cte3 UNION ALL SELECT * FROM cte4
;
/* Cleanup Testing Environment */
DROP TABLE IF EXISTS info_table;

结果为（根据运行顺序）：-

这些结果是适当的。

然后，只需将其中一个运行放入@Query的SQL中，并对其进行裁剪以传递变量：
例如：-

@Query("WITH " +
        "/* optional but allows testing outside of room  in sqlite tool*/" +
        "cte1(sortBy) AS (SELECT :sortBy)," + /* <<<<<<<<<< passed valued */
        " /* only 1 of the following will select any rows due to the WHERE clause*/" +
        "cte2 AS (SELECT * FROM info_table WHERE (SELECT sortBy FROM cte1) = 'START_PRIORITY' ORDER BY createddate DESC, starred DESC)," +
        "cte3 AS (SELECT * FROM info_table WHERE (SELECT sortBy FROM cte1) = 'TEXT_DESC' ORDER BY `TEXT` DESC)," +
        "cte4 AS (SELECT * FROM info_table WHERE (SELECT sortBy FROM cte1) = 'TEXT_ASC' ORDER BY `TEXT` ASC)" +
        " /* combine all three outputs 2 of which will return no rows */" +
        "SELECT * FROM cte2 UNION ALL SELECT * FROM cte3 UNION ALL SELECT * FROM cte4" +
        ";")
fun getSortedViaCTEs(sortBy: String): List<InfoTable>

为了实际证明这一点，与B、C和D相比，考虑以下活动代码：
db = TheDatabase.getInstance（this）dao = db. getAllDAO（）

dao.deleteAllInfoTableRows()
    dao.insert(InfoTable(createdDate = "2023-01-01", starred = "starred001", text = "ZZZZ"))
    dao.insert(InfoTable(createdDate = "2022-01-01", starred = "starred009", text = "YYYY"))
    dao.insert(InfoTable(createdDate = "2022-02-01", starred = "starred008", text = "XXXX"))
    dao.insert(InfoTable(createdDate = "2022-03-01", starred = "starred007", text = "WWWW"))

    logInfo("SP",dao.getSorted("START_PRIORITY"))
    logInfo("TD",dao.getSorted("TEXT_DESC"))
    logInfo("TA",dao.getSorted("TEXT_ASC"))

    logInfo("V2SP",dao.getSortedViaCTEs("START_PRIORITY"))
    logInfo("V2TD",dao.getSortedViaCTEs("TEXT_DESC"))
    logInfo("V2TA",dao.getSortedViaCTEs("TEXT_ASC"))

其中logInfo为：

fun logInfo(tagSuffix: String, infoTableExtract: List<InfoTable>) {
    for (i in infoTableExtract) {
        Log.d("DBINFO_$tagSuffix","ID = ${i.id} CreatedDate = ${i.createdDate} Starred = ${i.starred} Text = ${i.text}")
    }
}

结果**
从你的代码：-

2023-05-19 11:24:50.626 D/DBINFO_SP: ID = 2 CreatedDate = 2022-01-01 Starred = starred009 Text = YYYY
2023-05-19 11:24:50.626 D/DBINFO_SP: ID = 3 CreatedDate = 2022-02-01 Starred = starred008 Text = XXXX
2023-05-19 11:24:50.626 D/DBINFO_SP: ID = 4 CreatedDate = 2022-03-01 Starred = starred007 Text = WWWW
2023-05-19 11:24:50.626 D/DBINFO_SP: ID = 1 CreatedDate = 2023-01-01 Starred = starred001 Text = ZZZZ

2023-05-19 11:24:50.628 D/DBINFO_TD: ID = 1 CreatedDate = 2023-01-01 Starred = starred001 Text = ZZZZ
2023-05-19 11:24:50.628 D/DBINFO_TD: ID = 4 CreatedDate = 2022-03-01 Starred = starred007 Text = WWWW
2023-05-19 11:24:50.628 D/DBINFO_TD: ID = 3 CreatedDate = 2022-02-01 Starred = starred008 Text = XXXX
2023-05-19 11:24:50.628 D/DBINFO_TD: ID = 2 CreatedDate = 2022-01-01 Starred = starred009 Text = YYYY

2023-05-19 11:24:50.629 D/DBINFO_TA: ID = 1 CreatedDate = 2023-01-01 Starred = starred001 Text = ZZZZ
2023-05-19 11:24:50.629 D/DBINFO_TA: ID = 4 CreatedDate = 2022-03-01 Starred = starred007 Text = WWWW
2023-05-19 11:24:50.629 D/DBINFO_TA: ID = 3 CreatedDate = 2022-02-01 Starred = starred008 Text = XXXX
2023-05-19 11:24:50.629 D/DBINFO_TA: ID = 2 CreatedDate = 2022-01-01 Starred = starred009 Text = YYYY

显然不是所需的结果，例如TD和TA的顺序相同

根据CTE驱动查询：-

2023-05-19 11:24:50.632 D/DBINFO_V2SP: ID = 1 CreatedDate = 2023-01-01 Starred = starred001 Text = ZZZZ
2023-05-19 11:24:50.632 D/DBINFO_V2SP: ID = 4 CreatedDate = 2022-03-01 Starred = starred007 Text = WWWW
2023-05-19 11:24:50.632 D/DBINFO_V2SP: ID = 3 CreatedDate = 2022-02-01 Starred = starred008 Text = XXXX
2023-05-19 11:24:50.632 D/DBINFO_V2SP: ID = 2 CreatedDate = 2022-01-01 Starred = starred009 Text = YYYY

2023-05-19 11:24:50.636 D/DBINFO_V2TD: ID = 1 CreatedDate = 2023-01-01 Starred = starred001 Text = ZZZZ
2023-05-19 11:24:50.637 D/DBINFO_V2TD: ID = 2 CreatedDate = 2022-01-01 Starred = starred009 Text = YYYY
2023-05-19 11:24:50.637 D/DBINFO_V2TD: ID = 3 CreatedDate = 2022-02-01 Starred = starred008 Text = XXXX
2023-05-19 11:24:50.637 D/DBINFO_V2TD: ID = 4 CreatedDate = 2022-03-01 Starred = starred007 Text = WWWW

2023-05-19 11:24:50.639 D/DBINFO_V2TA: ID = 4 CreatedDate = 2022-03-01 Starred = starred007 Text = WWWW
2023-05-19 11:24:50.640 D/DBINFO_V2TA: ID = 3 CreatedDate = 2022-02-01 Starred = starred008 Text = XXXX
2023-05-19 11:24:50.640 D/DBINFO_V2TA: ID = 2 CreatedDate = 2022-01-01 Starred = starred009 Text = YYYY
2023-05-19 11:24:50.640 D/DBINFO_V2TA: ID = 1 CreatedDate = 2023-01-01 Starred = starred001 Text = ZZZZ

其他

在考虑了这个问题之后，CASE WHEN THEN ELSE END结构的顺序很重要，对它们进行重新排序可以获得预期的结果。
需要考虑的是，END之后的任何内容都包括在内。当第一个构造不匹配时，你会得到类似的东西：

ORDER BY DESC, createdDate DESC,  TEXT DESC, ASC

或

ORDER BY DESC, createdDate DESC, DESC, TEXT ASC

因此最高优先级排序将是createdDate DESC，因此TEXT列是第二排序，因此可能无用。
注意，没有解析的表达式基本上是相同的值，因此排序不受影响。
现在，如果你有：

@Query("SELECT * FROM info_table ORDER BY " +
        "CASE WHEN :sortBy = 'TEXT_DESC' THEN text END desc,"+
        "CASE WHEN :sortBy = 'TEXT_ASC' THEN text END asc," +
        "CASE WHEN :sortBy = 'START_PRIORITY' THEN starred END desc, createdDate desc"
)
fun getSortedV2(sortBy: String): List<InfoTable>

那么createdDate将始终是次要排序顺序。所以使用：-

logInfo("SP",dao.getSortedV2("START_PRIORITY"))
    logInfo("TD",dao.getSortedV2("TEXT_DESC"))
    logInfo("TA",dao.getSortedV2("TEXT_ASC"))

将导致：

2023-05-19 12:00:38.386 D/DBINFO_SP: ID = 2 CreatedDate = 2022-01-01 Starred = starred009 Text = YYYY
2023-05-19 12:00:38.386 D/DBINFO_SP: ID = 3 CreatedDate = 2022-02-01 Starred = starred008 Text = XXXX
2023-05-19 12:00:38.386 D/DBINFO_SP: ID = 4 CreatedDate = 2022-03-01 Starred = starred007 Text = WWWW
2023-05-19 12:00:38.386 D/DBINFO_SP: ID = 1 CreatedDate = 2023-01-01 Starred = starred001 Text = ZZZZ

2023-05-19 12:00:38.389 D/DBINFO_TD: ID = 1 CreatedDate = 2023-01-01 Starred = starred001 Text = ZZZZ
2023-05-19 12:00:38.389 D/DBINFO_TD: ID = 2 CreatedDate = 2022-01-01 Starred = starred009 Text = YYYY
2023-05-19 12:00:38.390 D/DBINFO_TD: ID = 3 CreatedDate = 2022-02-01 Starred = starred008 Text = XXXX
2023-05-19 12:00:38.390 D/DBINFO_TD: ID = 4 CreatedDate = 2022-03-01 Starred = starred007 Text = WWWW

2023-05-19 12:00:38.393 D/DBINFO_TA: ID = 4 CreatedDate = 2022-03-01 Starred = starred007 Text = WWWW
2023-05-19 12:00:38.393 D/DBINFO_TA: ID = 3 CreatedDate = 2022-02-01 Starred = starred008 Text = XXXX
2023-05-19 12:00:38.393 D/DBINFO_TA: ID = 2 CreatedDate = 2022-01-01 Starred = starred009 Text = YYYY
2023-05-19 12:00:38.393 D/DBINFO_TA: ID = 1 CreatedDate = 2023-01-01 Starred = starred001 Text = ZZZZ

即，命令现在看起来是正确的。

赞(0）回复(0）举报 2023-05-21

我来回答

如何在Android Studio中使用CASE为SQLite与Room请求参数时添加sencond排序项？

1条答案

相关问题

热门标签

最新问答