ios - SQLITE:INTERSECT/UNION 选择结果和法线数组

Question

我目前使用下面的解决方案，结果是我现在所期望的。

myIDArray = @[@1, @2, @3];
...
executeQuery:[NSString stringWithFormat:@""
            "SELECT name"
            "  FROM TABLE_A"
            " WHERE a_id IN ("
            "     SELECT b_id"
            "       FROM TABLE_B"
            " )"
            "   AND a_id IN %@", myIDArray];

但我想知道有没有什么方法可以INTERSECT IN (?) 内的两个id 列表？喜欢

executeQuery:[NSString stringWithFormat:@""
            "SELECT name"
            "  FROM TABLE_A"
            " WHERE a_id IN ("
            "     SELECT b_id"
            "       FROM TABLE_B"
            "  INTERSECT"
            "         %@"// how to put my id array here appropriately?
            " )", myIDArray];

当然这样不行，会抛出语法错误。

我还尝试使用 -componentsJoinedByString: 将数组转换为字符串，w/或 w/o () 用于字符串。运气不好。

Answer 1

INTERSECT 需要一个查询，因此您需要一个返回所有这些值的查询:

... IN (SELECT b_id FROM TableB
        INTERSECT
        SELECT id FROM (SELECT 1 AS id UNION ALL
                        SELECT 2       UNION ALL
                        ...
                        SELECT 42));

这比使用两个 IN 更冗长且效率更低。