如何在我的Android应用程序中连接两个SQLite表？

Question

背景

我有一个安卓项目，其中包含两个表：tbl_question 和 tbl_alternative。

为了使用游标填充视图中的问题和备选项，我正在使用游标。在尝试连接这两个表之前，获取所需数据没有任何问题。

    Tbl_question  
    -------------
    _id  
    question  
    categoryid  

    Tbl_alternative
    ---------------
    _id 
    questionid 
    categoryid 
    alternative

我想得到下面这样的结果：

SELECT tbl_question.question, tbl_alternative.alternative where 
categoryid=tbl_alternative.categoryid AND tbl_question._id = 
tbl_alternative.questionid.` 

这是我的尝试：

public Cursor getAlternative(long categoryid) {
            String[] columns = new String[] { KEY_Q_ID, KEY_IMAGE, KEY_QUESTION, KEY_ALT, KEY_QID};
             String whereClause = KEY_CATEGORYID + "=" + categoryid +" AND "+ KEY_Q_ID +"="+ KEY_QID;
             Cursor cursor = mDb.query(true, DBTABLE_QUESTION + " INNER JOIN "+ DBTABLE_ALTERNATIVE, columns, whereClause, null, null, null, null, null);
             if (cursor != null) {
                  cursor.moveToFirst();
             }
             return cursor;

我发现这种形式的查询比普通的SQL更难，但是得到的建议是使用这种方式，因为它更少出错。

问题

如何在我的应用程序中连接两个SQLite表？

- kakka47

---

你遇到了什么错误？你尝试过用字符串字面量替换连接的字符串进行测试吗？（顺便说一句，自从1986年以来我就没有使用过游标。但我不开发Android。） - Mike Sherrill 'Cat Recall'

我没有看到游标代码块中指定内连接列的位置/方式。SQL 语句应该是 "select desired-cols-list from T1 inner join T2 on T1.questionid =T2.questionid and T1.categoryid = T2.categoryid where T1.categoryid = {the desired category value}"。 - Tim

这是我的错误信息：ambiguous column name: _id。在编译以下语句时出现此问题：SELECT DISTINCT _id, image, question, alternative, questionid FROM tbl_question INNER JOIN tbl_alternative WHERE categoryid = 2 AND _id = questionid。因此，我认为我需要指定 tbl_question._id = tbl_alternative.questionid，但我不知道如何在上述查询方式中实现此操作。如果使用“常规”SQL语法：“Select ' from tbl_question INNER JOIN tbl_alternative ON tbl_question._id = tbl_alternative.questionid AND tbl_question.categoryid = tbl_alternative.categoryid = categoryid，则不知道该返回什么。”。 - kakka47

@Tim：你这样做，我应该如何在db-helper类中形成方法？我不应该返回一个游标吗？我正在学习过程中，如果有任何可以使我的代码更好的建议，我将不胜感激！ - kakka47

我在这里发布了答案 https://stackoverflow.com/questions/31567564/sqllitedatabase-query-for-inner-join/55333187#55333187 - Sagar

Answer 1

您需要使用rawQuery方法。

示例：

private final String MY_QUERY = "SELECT * FROM table_a a INNER JOIN table_b b ON a.id=b.other_id WHERE b.property_id=?";

db.rawQuery(MY_QUERY, new String[]{String.valueOf(propertyId)});

使用 ? 绑定而不是将值放入原始的 SQL 查询中。

- pawelzieba

---

@necromancer，你认为创建一个“VIEW”比使用“rawQuery”更好的选择是什么？ - Muhammad Babar

查询之后应该做什么？如何知道哪个id属于哪个表，如果两个表对于某一列有相同的列名怎么办？或者第二个表中有多个项目怎么办？ - android developer

@android-developer "查询之后应该做什么？" - 你会得到一个Cursor，可以随意处理数据 :P; 2. "如何知道哪个id..." - 这是一个JOIN操作，所以根据类型的不同，你可能会得到总是、从不或者可能有值的键；3. "...某些列具有相同的列名" - 可能会发生，但是可以消除歧义。老实说，我不知道是否可以使用getColumnIndex获取"Table.column"，你可以手动计算索引，进行调试测试；4. "第二个表中有多个项目"也取决于连接类型。 - leRobot

假设View是1对多关系的FULL OUTER JOIN（例如RawContactsEntity），您可以获取在“多”表上具有空KEY的行，但从不在“1”表上，因此您应该像这样分支您的游标传递：while (cursor.moveToNext() { if (cursor.getValue(getManyTableKeyIndex()) == NULL) { /* 这是一个无关联行，只有“1”表数据 / } else { / 这是一个关联HIT，因此两个表都有数据 */ } } cursor.close(); - leRobot

我忘记为“没有父母的多行”添加另一个分支else，即使使用FULL OUTER JOIN仍然可以获得这些行，但如果关系是严格的（我不确定Android是否强制执行关系），通常不会出现这种情况。不知道如何获取模糊列，但您可以通过声明具有列歧义的视图并查看在“SELECT * FROM view_x”查询中这些列的名称来在任何SQLite CLI上进行测试。然后，您只需将其应用于您想要的任何Android helper flavor（.query() / .rawQuery() ...）。 - leRobot

Answer 2

另一种方法是构建一个视图，然后像查询表一样对其进行查询。 在许多数据库管理器中，使用视图可以提高性能。

CREATE VIEW xyz SELECT q.question, a.alternative  
   FROM tbl_question AS q, tbl_alternative AS a
  WHERE q.categoryid = a.categoryid 
    AND q._id = a.questionid;

这是记忆中的内容，所以可能存在一些语法问题。 http://www.sqlite.org/lang_createview.html

我提到这种方法是因为你可以使用视图和SQLiteQueryBuilder，正如你所暗示的那样它更可取。

- phreed

---

对于像Oracle这样的关系型数据库管理系统，视图可以带来一些性能提升，但根据我的经验，它们仅在需要进行性能或报告方面时才被使用。换句话说，您不需要为每个联接查询创建一个视图。特别是在SQLite中，我认为没有任何性能增益——根据这个问题的答案，SQLite会使用子查询重写查询。 Android的API可能对OP正在做的事情有所限制，但rawQuery()是正确的答案。 - spaaarky21

Answer 3

除了 @pawelzieba 的回答之外，他的回答是正确的，要连接两个表，你可以像这样使用 INNER JOIN：

SELECT * FROM expense INNER JOIN refuel
ON exp_id = expense_id
WHERE refuel_id = 1

通过像这样的原始查询 -

String rawQuery = "SELECT * FROM " + RefuelTable.TABLE_NAME + " INNER JOIN " + ExpenseTable.TABLE_NAME
        + " ON " + RefuelTable.EXP_ID + " = " + ExpenseTable.ID
        + " WHERE " + RefuelTable.ID + " = " +  id;
Cursor c = db.rawQuery(
        rawQuery,
        null
);

由于SQLite具有向后兼容的原始查询支持，我们将该命令转换为以下内容 -

SELECT *
FROM expense, refuel
WHERE exp_id = expense_id AND refuel_id = 1

因此能够利用SQLiteDatabase.query()辅助方法。

Cursor c = db.query(
        RefuelTable.TABLE_NAME + " , " + ExpenseTable.TABLE_NAME,
        Utils.concat(RefuelTable.PROJECTION, ExpenseTable.PROJECTION),
        RefuelTable.EXP_ID + " = " + ExpenseTable.ID + " AND " + RefuelTable.ID + " = " +  id,
        null,
        null,
        null,
        null
);

如需详细的博客文章，请查看http://blog.championswimmer.in/2015/12/doing-a-table-join-in-android-without-using-rawquery

- Arnav Gupta

---

我不明白Utils.concat是从哪里来的。 - nicoqueijo

这是一个不错的方法，但如果一个表中的列名与另一个表中的列名相同，则无法正常工作。例如，如果两个表都有一个名为ID的列，则会抛出“android.database.sqlite.SQLiteException：ambiguous column name”的异常。 - Anup Sharma

Answer 4

“模棱两可的列”通常意味着同一列名称出现在至少两个表中；数据库引擎无法确定您想要哪一个。使用完整的表名或表别名来消除歧义。

以下是我编辑器中遇到的一个例子。它来自别人的问题，但应该是有意义的。

select P.* 
from product_has_image P
inner join highest_priority_images H 
        on (H.id_product = P.id_product and H.priority = p.priority)

- Mike Sherrill 'Cat Recall'

---

是的，我也怀疑这一点。但我不知道如何指定表格，因为这种方式：DBTABLE_QUESTION.KEY_QUESTION，无法工作。但当我使用rawQuery方式时，很容易定义哪个表格属于哪个列。 - kakka47