在数据库浏览器中运行罚款的sql查询中的Android异常

Question

在Android 上运行以下查询时

select * 
from EICRChecklistItems 
where (Sect, S_Sect, SS_Sect) in (
select Sect, S_Sect, SS_Sect 
from EICRCheckList 
where Version=2018 
order by id
) 
order by id

我得到了一个例外：

android.database.sqlite.SQLiteException：near"quot；：语法错误(代码1(

列Sect、S_Sect和SS_Sect都定义为integer。

但当在DB浏览器上针对同一数据库的副本运行时，它会正确执行

任何可能导致这种情况的指针都将不胜感激。

private var EICR_CHECK_ITEMS_TABLE = "EICRChecklistItems"
private var EICR_CHECKLIST_TABLE = "EICRCheckList"
val cursor1: Cursor = writableDatabase.rawQuery(
"select * from $EICR_CHECK_ITEMS_TABLE where (Sect, S_Sect, SS_Sect) in (select Sect, S_Sect, SS_Sect from $EICR_CHECKLIST_TABLE where Version='2018' order by id) order by id",  null)

Answer 1

由于您的SQLite版本在3.15.0之前，并且您不能使用ROW VALUES，因此您可以使用EXISTS而不是运算符IN:编写查询

SELECT ei.* 
FROM EICRChecklistItems ei 
WHERE EXISTS (
SELECT 1 FROM EICRCheckList el
WHERE el.Version = 2018
AND el.Sect = ei.Sect AND el.S_Sect = ei.S_Sect AND el.SS_Sect = ei.SS_Sect
)
ORDER BY ei.id

请注意，代码中子查询中的ORDER BY子句实际上是无用的