在mysql中创建关系表 [英] creating relational tables in mysql

问题描述

尝试尽可能最好地解释这一点:

An attempt to explain this as best as possible:

首先，我有一个名为 vocabulary 的表:

To begin, I have a table called vocabulary:

+-----+---------------------+
| id  |  name               |
+-----+---------------------+
|  1  |  Farming            |
|  2  |  Agriculture        |
|  3  |  Design             |
|  4  |  Graphic Design     |
|  5  |  Interactive Design |
|  6  |  Animal Husbandry   |
| ... |          ...        |
| 887 |  Carpentry          |
+-----+---------------------+

在一个 excel 表中，我有一个相关词汇项目"的列表，它们可能看起来像:

In an excel sheet, I have lists of 'related vocabulary items' which might look like:

Interactive Design                       Farming
--------------------                     --------------------
Graphic Design                           Agriculture
Design                                   Animal Husbandry

我想要做的是创建一个名为 vocabulary_relations 的表格，并在标题(在上面的 Excel 表格示例中)与其下方的每个项目之间创建关系.例如，交互设计将指向平面设计和设计.农业将指向农业和畜牧业.

What I want to do is create a table called vocabulary_relations and create relationships between the header (in the above excel sheet example) to every item below it. For example, Interactive Design would point to Graphic Design and Design. Farming would point to Agriculture and Animal Husbandry.

例如，该表将如下所示(使用 vocabulary 表作为参考):

For example, the table would look like this (using the vocabulary table as reference):

+----------------+--------------+
| vocabulary_id  |  related_id  |
+----------------+--------------+
|       5        |       4      |
|       5        |       2      |
|       1        |       2      |
|       1        |       6      |
+----------------+--------------+

那样，我可以为任何vocabulary_id提取所有related_id.

That way, I can pull all the related_id for any vocabulary_id.

例如，如果我请求 vocabulary_id = 1 (farming)，它将返回 related_id =>2、6(分别为农业、畜牧业).

For example, if I requested vocabulary_id = 1 (farming) it would return related_id => 2, 6 (agriculture, animal husbandry, respectively).

有没有人对如何自动化这样的事情有任何想法?我不是在寻找用户界面，这是静态数据.

谢谢！

推荐答案

INSERT INTO vocabulary_relations
    (vocabulary_id, related_id)
    SELECT v1.id, v2.id
        FROM RelatedItems ri
            INNER JOIN vocabulary v1
                ON ri.col1 = v1.name
            INNER JOIN vocabulary v2
                ON ri.col2 = v2.name

这篇关于在mysql中创建关系表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！