在 Sqlite 中使用整数'in'子句

Question

我在其中一个表中有一个Integer列（响应）。我必须使用 In 子句才能获得结果。但这行不通。这就是我正在做的：

NSString *responseString = @"";
    if(filter.isAttending && filter.isDeclined && filter.isNotResponded)
        responseString = @"0,1,3";
    else if(filter.isAttending && filter.isDeclined && !filter.isNotResponded)
        responseString = @"0,1";
    else if(filter.isAttending && !filter.isDeclined && filter.isNotResponded)
        responseString = @"0,3";
    else if(!filter.isAttending && filter.isDeclined && filter.isNotResponded)
        responseString = @"1,3";
    else if(filter.isAttending && !filter.isDeclined && !filter.isNotResponded)
        responseString = @"0";
    else if(!filter.isAttending && !filter.isDeclined && filter.isNotResponded)
        responseString = @"3";
    else if(!filter.isAttending && filter.isDeclined && !filter.isNotResponded)
        responseString = @"1";
    else if(!filter.isAttending && !filter.isDeclined && !filter.isNotResponded)
        responseString = @"-1";  

const char *sql  = sql = "SELECT * FROM Activity Inner Join Invitation on Invitation.ActivityId = Activity.ActivityId Where Invitation.Response in (?) AND Activity.IsDeleted = ? AND Activity.IsAdmin = ? AND Activity.StartDate <= DateTime('now')";  

这就是我绑定值的方式：

sqlite3_bind_text(statement, 1, [responseString UTF8String], -1, SQLITE_TRANSIENT);
sqlite3_bind_int(statement, 2, isDeleted);
sqlite3_bind_int(statement, 3, [isAdmin intValue]);

我从中没有得到任何结果。我怀疑的是，查询变成了：

SELECT * FROM Activity Inner Join Invitation on Invitation.ActivityId =   Activity.ActivityId Where Invitation.Response in ("0,1,3") AND Activity.IsDeleted = 1 AND Activity.IsAdmin = 251697 AND Activity.StartDate >= DateTime('now')  

请注意，它是 （"0,1,3"） 而不是 （0,1,3）。

但是当我在 Sqlite 管理器中编写此查询时，它给了我正确的结果：

SELECT * FROM Activity Inner Join Invitation on Invitation.ActivityId =   Activity.ActivityId Where Invitation.Response in (0,1,3) AND Activity.IsDeleted = 1 AND Activity.IsAdmin = 251697 AND Activity.StartDate >= DateTime('now') 

Answer 1

你的怀疑是有道理的; sqlite3_bind_text()绑定单个文本值。

要绑定多个整数值，您必须将参数标记的适当数量插入到 SQL 字符串 （ ... IN (?,?,?) ），并为每个参数标记调用 sqlite3_bind_int()。

（使用 %@ 将数字直接放入 SQL 查询中可能更容易。