表连接——多行到一列（/单元格）/从一列（/单元格）
我已经寻找了这个问题的解决方案，但还没有找到它，可能是因为我自己不太知道如何正确解释它。如果它已经发布在某个地方，请告诉我。
我拥有的是三个彼此相关的数据库；主要、部分和组。基本上，主数据库包含帖子中最基本/最常用的信息，而片段数据库包含与该帖子相关的数据。组数据库包含主数据库中的帖子可以“发布”的组的所有（长）名称。一个帖子可以同时在多个组中发布。当新帖子添加到我的网站时，我也会检查这些帖子是否有重复项（检查该帖子是否已发布）。为了使重复搜索更加有效，我只检查同一组中发布的文章。
希望你仍然和我在一起，因为我认为这开始变得非常令人困惑（如果我需要更清楚地指定事情，请告诉我）：现在，主数据库和片段数据库都包含组的全名(s)（基本上我根本不使用组数据库）。我想要做的是将这些组的名称替换为组数据库中的关联 ID。例如，我想更改此：
来自：
MAIN_table：
id | group_posted_in
--------|----------------------------------------
1 |组_1、组_5
2 |组_15、组_75
3 |组_1、组_215
组表：
id | 组名
--------|----------------------------------------
1 |组_1
2 |组_2
3 |组_3
ETC...
进入：
主表：
id | group_posted_in
--------|----------------------------------------
1 | 1,5
2 | 15,75
3 | 1,215
或者类似的东西。但是，这种格式特别会导致问题，因为以下查询将返回所有行（来自示例），而不仅仅是我需要的行：
SELECT * FROM main_table WHERE group = '5'
我要么必须将查询更改为如下所示：
...WHERE group = '5' OR group = '5,%' OR group = '%,5,%' OR group = '%,5'
要么我必须更改数据库结构从逗号分隔值变为如下所示：[15][75]。附带的查询会更简单，但对我来说这似乎是一个麻烦的解决方案。此外，（简单的）连接根本不容易/不可能。它总是要求我运行一个单独的查询来获取组的名称 - 用户是否搜索特定组中的帖子（在这种情况下，我首先必须运行一个查询来获取 id，然后搜索相关的帖子），或者是否显示它们（首先是帖子，然后是另一个匹配组的查询）。
所以，总而言之：我想我知道这个问题有一个解决方案，但我的直觉告诉我这不是正确/最好的方法。因此，我认为将这篇文章联系在一起的问题是：
将组数据库连接到其他数据库的正确方法是什么？
I have searched for a solution for this problem, but haven't found it (yet), probably because I don't quite know how to explain it properly myself. If it is posted somewhere already, please let me know.
What I have is three databases that are related to each other; main, pieces & groups. Basically, the main database contains the most elementary/ most used information from a post and the pieces database contains data that is associated with that post. The groups database contains all of the (long) names of the groups a post in the main database can be 'posted in'. A post can be posted in multiple groups simultaneously. When a new post is added to my site, I check the pieces too see if there are any duplicates (check if the post has been posted already). In order to make the search for duplicates more effective, I only check the pieces that are posted in the same group(s).
Hopefully you're still with me, cause here's where it starts to get really confusing I think (let me know if I need to specify things more clearly): right now, both the main and the pieces database contain the full name of the group(s) (basically I'm not using the groups database at all). What I want to do is replace the names of those groups with their associated IDs from the groups database. For example, I want to change this:
from:
MAIN_table:
id | group_posted_in
--------|---------------------------
1 | group_1, group_5
2 | group_15, group_75
3 | group_1, group_215
GROUPS_table:
id | group_name
--------|---------------------------
1 | group_1
2 | group_2
3 | group_3
etc...
into:
MAIN_table:
id | group_posted_in
--------|---------------------------
1 | 1,5
2 | 15,75
3 | 1,215
Or something similar to this. However, This format specifically causes issues as the following query will return all of the rows (from the example), instead of just the one I need:
SELECT * FROM main_table WHERE group = '5'
I either have to change the query to something like this:
...WHERE group = '5' OR group = '5,%' OR group = '%,5,%' OR group = '%,5'
Or I have to change the database structure from Comma Separated Values to something like this: [15][75]. The accompanying query would be simpler, but it somehow seems like a cumbersome solution to me. Additionally, (simple) joins will not be easy/ possible at all. It will always require me to run a separate query to fetch the names of the groups--whether a user searches for posts in a specific group (in which case, I first have to run a query to fetch the id's, then to search for the associated posts), or whether it is to display them (first the posts, then another query to match the groups).
So, in conclusion: I suppose I know there is a solution to this problem, but my gut tells me that it is not the right/ best way to do it. So, I suppose the question that ties this post together is:
What is the correct method to connect the group database to the others?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
对于多对多关系，您需要创建连接表。您不应将组列表存储在单个列中，而应将该列拆分为单独表中的多行。这将允许您对它们执行基于集合的函数，并将显着加快数据库速度，并使其更加健壮和防错。
因此，对于 MainID 1，您将拥有 GroupsInMain 记录：
这将组 1 和 5 与 MainID 1
FK 相关联，在这种情况下意味着外键（即对另一个表中主键的引用）。您可能还想在 MainID 和 GroupID 上向 GroupsInMain 添加唯一约束，因为您永远不希望配对的相同值多次出现。
那么您的查询将是：
For a many-to-many relationship, you need to create a joining table. Rather than storing a list of groups in a single column, you should split that column out into multiple rows in a separate table. This will allow you to perform set based functions on them and will significantly speed up the database, as well as making it more robust and error proof.
So, for MainID 1, you would have GroupsInMain records:
This associates groups 1 and 5 with MainID 1
FK in this case means a Foreign Key (i.e. a reference to a primary key in another table). You'd probably also want to add a unique constraint to GroupsInMain on MainID and GroupID, since you'd never want the same values for the pairing to show up more than once.
Your query would then be: