如何在SQLite连接表中取不同的值？

Question

query = "SELECT" +
            " e.id AS _id," +
            " ke.id AS ke_id," +
            " ke.fk as ke_fk," +
            " ke.value as ke_value," +
            " re.id AS ke_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_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";

我有一张“gloss”桌子。我在“value”中有一列。在联接表中为'g_value‘。例如，当我搜索"girl“时，它会找到五个重复的结果。

​

​

如何获取'g_value‘的不同值以避免结果中的重复？

Answer 1

我认为您应该通过使用GROUP BY来消除重复项。如下所示：

query = "SELECT" +
            " e.id AS _id," +
            " ke.id AS ke_id," +
            " ke.fk as ke_fk," +
            " ke.value as ke_value," +
            " re.id AS ke_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_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+"%' " +
            " GROUP BY " +
            " LIMIT 20";