android房间数据库Dao两个查询？

Question

如何使用一个@Query语句返回两个查询的结果？

我有一个单表的项目数据库。每个项目都有一个到期日期(在Room数据库中保存为一个长日期)或没有到期日(在数据库中保存为-1 )。我希望有一个查询，该查询以升序返回所有具有到期日期的项，然后返回所有剩余的项，并按保存在数据库中的时间戳排序。时间戳表示项目最初保存到Room数据库时的日历日期和时间。

下面是我期望的输出的一个例子，使用美国日历作为截止日期：

1. 8/ 17 /2022 (2022年8月17日截止日期)
2. 2022(到期日)
3. 12/15/2022 (到期日)
4. 5601时间戳(无到期日)
5. 4200时间戳(无到期日)
6. 1150时间戳(无到期日)

Dao中的以下查询返回查询的第一部分(升序到期日期)的预期结果。那么，如何将下面的查询附加到第二部分，其中还返回没有到期日的项目，并按降序显示它们的时间戳？我尝试了多种方法使用UNION，UNION ALL等，但没有运气。

@Query(“从cardDuedatentime !=-1由cardDuedatentime ASC订购的卡片中选择*”)

Answer 1

首先，通过布尔表达式cardDuedatentime = -1进行排序，以获得结果集底部没有到期日的所有行。

然后，使用带有CASE表达式的条件排序来排序没有到期日降序的行和具有有效到期日升序的行：

SELECT *
FROM cards
ORDER BY cardDuedatentime = -1,
         CASE WHEN cardDuedatentime = -1 THEN -timestamp ELSE cardDuedatentime END;

如果只需要结果中的一列：

SELECT CASE WHEN cardDuedatentime = -1 THEN timestamp ELSE cardDuedatentime END time
FROM cards
ORDER BY cardDuedatentime = -1,
         CASE WHEN cardDuedatentime = -1 THEN -timestamp ELSE cardDuedatentime END;

见演示。

Answer 2

如果我正确理解你的问题，那么我相信你可以用：-

@Query("WITH cte1 AS (SELECT * FROM cards WHERE cardDueDatentime != -1 ORDER BY cardDueDatentime ASC),cte2 AS (SELECT * FROM cards WHERE cardDueDatentime = -1 ORDER BY timestamp ASC) SELECT * FROM cte1 UNION ALL SELECT * FROM cte2;")

下列测试/演示：

DROP TABLE IF EXISTS cards;
CREATE TABLE IF NOT EXISTS cards (cardDueDatentime INTEGER,timestamp INTEGER, othercolumns TEXT);
INSERT INTO cards VALUES 
    (strftime('%s','2022-08-17'),strftime('%s','now'),'A')
        ,(-1,5601,'A')
    ,(strftime('%s','2022-08-11'),strftime('%s','now'),'A')
        ,(-1,4201,'A')
    ,(strftime('%s','2022-12-15'),strftime('%s','now'),'A')
    ,(-1,1150,'A')
;

WITH 
    cte1 AS (SELECT * FROM cards WHERE cardDueDatentime != -1 ORDER BY cardDueDatentime ASC),
    cte2 AS (SELECT * FROM cards WHERE cardDueDatentime = -1 ORDER BY timestamp ASC)
SELECT * FROM cte1 UNION ALL SELECT * FROM cte2;
DROP TABLE IF EXISTS cards; 

执行上述操作的结果(使用Navicat for SQLite工具)：-

​

​

使用了两个CTE(公共表表达式，也称为临时表)，每个表都用于提取一组数据--重要的是--对它们进行独立的排序。然后，通过UNION和(未排序的)组合它们(因为排序会影响完整的数据集)。

• 注意数据是如何有目的地插入的，这样就不能对数据进行适当的排序。

这是上面的一个SQLFiddle

一个更简单的方法是使用：

@Query("SELECT * FROM cards ORDER BY cardDueDatentime=-1 ASC,cardDueDatentime ASC, timestamp ASC;")

其中使用上述数据的结果是相同的。这是因为cardDueDatentime=-1将等同于true (1)或false (0)。因此-1将等于1，而有效的日期时间将等于0，因此有效的日期时间在无效的(-1)日期时间之前。然后，后续的排序字段相应地对每一组进行排序。

如果您想要任何无效日期(les大于0)，则可以使用以下内容：-

@Query("SELECT * FROM cards ORDER BY cardDueDatentime<0 ASC,max(CAST(cardDueDatentime AS INTEGER),0) ASC, timestamp ASC;")

因此，如果插入了其他行，如:-

....
,(-2,111,'B')
,(-3,11,'C')
,(-1,1,'X')

其结果是：-

​

​

对于第一个更简单的选择，使用附加数据，结果(错误)将是:-

​

​

• 也就是说，对于C行as -3不等于-1，那么它就像它是一个有效日期，
• 因此，<0将其视为无效日期，因此它包含在无效日期集中；
• 但是，如果<0允许-3进入无效日期集，那么cardDueDatentime上的第二个排序将放在-3在-2之前和-1之前，因此max函数的值小于-1，使它们成为-1，从而-3变成-1 (与所有其他无效日期一样)，因此第三个排序字段是无效日期集中的适用排序字段。

- this could be useful if you for some reason wanted to have different sets/types of invalid dates but not affect the query.