错误：无关输入"." 期望 {，在安卓房间查询中

Question

在Android房间中使用以下查询：

@Query(
"SELECT strftime ('%d-%m-%Y', datetime(DISTINCT timeStamp, 'unixepoch', 'localtime')) AS e.name " +
"FROM (SELECT batteryDetails.*, " +
"COUNT(logId) OVER" +
"(PARTITION BY batteryId) AS oldStatus " +
"from batteryDetails) batteryDetails " +
"LEFT JOIN eventTypes e ON e.eventType = batteryDetails.status " +
"WHERE oldStatus is null OR oldStatus <> status AND batteryId = :batteryId AND " +
"timeStamp between :startDate AND :endDate " +
"ORDER BY timeStamp DESC"
)
suspend fun getHistoryEventsData(
batteryId: String,
startDate: String,
endDate: String
): List<String>?

getHistoryEventsData是一个函数

此查询在SQLite数据库浏览器中成功运行。

但它在运行应用程序时给了我以下错误：

错误：外部输入"."应为｛，'；'，K_ALTER，K_ANALYZE，K_ATTACH，K_BEGIN，K_COMMIT，K_CREATE，K_DELETE，K_DETACH，K_DROP，K_END，K_EXPLAIN，K_INSERT，K_PRAGMA，K_REINDEX，K_RELEASE，K_REPLACE，K_ROLLBACK，K_SAVEPOINT，K_SELECT，K_UPDATE，K_VACUUM，K_VALUES，K_WITH，UNEXPECTED_CHAR｝公共抽象java.lang.Object getHistoryEventsData(@org.jetbrains.annotations.NotNull((^外来输入"("应为｛，'；'，K_ALTER，K_ANALYZE，K_ATTACH，K_BEGIN，K_COMMIT，K_CREATE，K_DELETE，K_DETACH，K_DROP，K_END，K_EXPLAIN，K_INSERT，K_PRAGMA，K_REINDEX，K_RELEASE，K_REPLACE，K_ROLLBACK，K_SAVEPOINT，K_SELECT，K_UPDATE，K_VACUUM，K_VALUES，K_WITH，UNEXPECTED_CHAR｝

可能是什么问题？

我的SQLite版本是3.28.0，使用roomRuntime=3.2.0

Answer 1

查询中有2个语法错误

第一种是在函数strftime()中使用关键字DISTINCT(这实际上是SQLite允许的，但其行为与应有的不同(。也许你想在函数之前使用它
第二种方法是使用别名e在其定义中限定列别名name的名称。不应限定别名的定义

更改为：

SELECT DISTINCT strftime('%d-%m-%Y', datetime(timeStamp, 'unixepoch', 'localtime')) AS name 
FROM (
SELECT batteryDetails.*, 
COUNT(logId) OVER(PARTITION BY batteryId) AS oldStatus 
from batteryDetails
) batteryDetails 
LEFT JOIN eventTypes e ON e.eventType = batteryDetails.status 
WHERE oldStatus is null OR oldStatus <> status 
AND batteryId = :batteryId 
AND timeStamp between :startDate AND :endDate 
ORDER BY timeStamp DESC;

此外，这个：

strftime('%d-%m-%Y', datetime(timeStamp, 'unixepoch', 'localtime'))

可以简化为：

strftime('%d-%m-%Y', timeStamp, 'unixepoch', 'localtime')