许多一对多查询的一个光标不重复数据 [英] Many-to-Many Query in One Cursor Without Repeating Data

问题描述

所以，我会说，我有三个表如下：

So, I'm going to say that I have three tables as follows:

       POSTS             POSTS_TAGS           TAGS
+-----+-----------+ +---------+--------+ +-----+-------+
| _id |   title   | | post_id | tag_id | | _id | title | 
+-----+-----------+ +---------+--------+ +-----+-------+
|  0  |    foo    | |    0    |    1   | |  0  |  baz  |
+-----+-----------+ +---------+--------+ +-----+-------+
|  1  |    bar    | |    0    |    2   | |  1  | quux  |
+-----+-----------+ +---------+--------+ +-----+-------+
                    |    1    |    0   | |  2  | corge |
                    +---------+--------+ +-----+-------+
                    |    1    |    2   |
                    +---------+--------+

有没有什么办法来制订使用SQLite的查询，这样我可以再有一个光标在它下面的数据：

Is there any way to formulate a query with SQLite such that I could then have a cursor with the following data in it:

ROW1 =＆LT;富，＆LT; QUUX，corge＆GT;＆GT;
ROW2 =＆LT;酒吧，＆LT;巴兹，corge＆GT;＆GT;

row1 = <foo, <quux, corge>>
row2 = <bar, <baz, corge>>

至于反对：

ROW1 =＆LT;富，QUUX＆GT;
ROW2 =＆LT;富，corge＆GT;
ROW3 =＆LT;酒吧，巴兹＆GT;
ROW4 =＆LT;酒吧，corge＆GT;

row1 = <foo, quux>
row2 = <foo, corge>
row3 = <bar, baz>
row4 = <bar, corge>

不过，我严重怀疑，有什么事，这将使我precisely这一点，所以我想我真正的问题是，什么是制定像这样的查询最好的方式，这样我就可以传回我的活性，它能够对所有这些数据返回到活动？还是我真的需要通过我的光标迭代之后再次向捡所有额外的数据，并重新组织自己。

However, I severely doubt that there is anything that will give me precisely that, so I guess my real question is, what is the best way to formulate a query such as this, so that I can pass it back to my activity, and it would be able to return all this data to the activity? Or am I really going to need to iterate through my cursor again afterwards to "pick up" all the extra data and reorganize it myself.

推荐答案

得到了解决：

SELECT posts._id, posts.title, GROUP_CONCAT(tags._id) AS tags_id, GROUP_CONCAT(tags.tag_name) AS tags_name FROM posts 
LEFT OUTER JOIN posts_tags ON posts_tags.post_id=posts.post_id
LEFT OUTER JOIN tags ON posts_tags.tag_id=tags.tag_id
GROUP BY posts._id;

请调整查询到你的问题：）

Please adapt the query to your problem :)

您可以找到有关 CONCAT（X）和 CONCAT（X，分离器）的这里。

反正你也可以按照我对谷歌组问题

这篇关于许多一对多查询的一个光标不重复数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！