在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:“,”附近:语法错误(代码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)
由于您的SQLite版本在3.15.0之前,并且您不能使用行值
,因此您可以使用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
子句实际上是无用的。