如何使用相同列名的已连接表的值？

Question

MainActivity.java：

    private void displayListView() {
    cursor = myDatabase.getJoinedInfo(etSearch.getText().toString());

    String[] columns = new String[] { myDatabase.RE_VALUE,
            myDatabase.GL_VALUE, myDatabase.KE_VALUE };

    int[] to = new int[] { R.id.tvHiragana, R.id.tvMeaning, R.id.tvKanji };

    dataAdapter = new SimpleCursorAdapter(this, R.layout.wordonlist,
            cursor, columns, to, 0);

    ListView listView = (ListView) findViewById(R.id.lvWordlist);
    // Assign adapter to ListView

    listView.setAdapter(dataAdapter);
}

MyDatabase.java：

public static final String GL_TABLE="gloss";
public static final String GL_ID="id";
public static final String GL_LANG="lang";
public static final String GL_VALUE="value";

public static final String KE_ID="id";
public static final String KE_VALUE="value";

public static final String RE_ID="id";
public static final String RE_VALUE="value";

public static final String LOG_TAG="myLogs";

public Cursor getJoinedInfo(String lookingFor)
{
    Log.d(LOG_TAG, "DB: looking up info");

    SQLiteDatabase db=getReadableDatabase();

    Cursor cursor;
    String query;

    query="SELECT " +
            " e.id AS _id," +
            " ke.id AS _id," +
            " ke.fk," +
            " ke.value," +
            " re.id AS _id," +
            " re.fk," +
            " re.value," +
            " s.id AS _id," +
            " s.fk," +
            " g.id AS _id," +
            " g.fk," +
            " g.lang," +
            " g.value" +
            " FROM entry e" +
            "     INNER JOIN k_ele ke ON e.id = ke.fk" +
            "     INNER JOIN r_ele re ON e.id = re.fk" +
            "     INNER JOIN sense s ON e.id = s.fk" +
            "     INNER JOIN gloss g ON s.id = g.fk" +
            " WHERE g.value like '%"+lookingFor+"%' LIMIT 20";

    Log.d(LOG_TAG, "DB: query = \n" + query.replace(", ",",\n  "));
    cursor=db.rawQuery(query,null);
    Log.d(LOG_TAG, "DB: query complete");

    return cursor;      
}

我有五张桌子。其中3个列的名称为"value“。游标只接受一个表的“值”，而忽略其他表。与搜索字符串匹配的所有值都是相似的！请给出一些解决这个问题的建议。

​

​

Answer 1

AS关键字的全部目的是为一个列指定另一个名称，该名称在所有检索到的列中都是唯一的。您只在其中一些上使用它，并且总是分配相同的列名。查询应该如下所示

query = "SELECT " +
        " e.id AS e_id," +
        " ke.id AS ke_id," +
        " ke.fk as ke_fk," +
        " ke.value as ke_value," +
        " re.id AS re_id," +
        " re.fk as re_fk," +
        " re.value as re_value," +
        " s.id AS s_id," +
        " s.fk as s_fk," +
        " g.id AS g_id," +
        " g.fk as g_fk," +
        " g.lang," +
        " g.value as g_lang" +
        " FROM entry e" +
        "     INNER JOIN k_ele ke ON e.id = ke.fk" +
        "     INNER JOIN r_ele re ON e.id = re.fk" +
        "     INNER JOIN sense s ON e.id = s.fk" +
        "     INNER JOIN gloss g ON s.id = g.fk" +
        " WHERE g.value like '%"+lookingFor+"%' LIMIT 20";

这样，结果集中的每一列都会有不同的名称。您还应该改进您的命名，因为g，s，e，ke和re这样的名称并不意味着什么，并且使代码难以阅读和理解。