如何形成MySQL查询算法以将列添加到另一个表中

Question

我有两个表格，如下所示。

句子

     +--------+----------+-----------------------------------------------------------------------+
     | id     | language | text                                                                  |
     +--------+----------+-----------------------------------------------------------------------+
     | 519125 | epo      | Mia onklo aĉetis por mi horloĝon kiu estas tre akurata.               |
     | 519126 | epo      | Mi ne povas akcepti vian donacon.                                     |
     | 519127 | uig      | ئۇ پۈتۈن مەملىكەتنى ئارىلاپ چىقماقچى.                                   |
     | 519128 | eng      | She's planning to travel the entire country.                          |
     | 519129 | eng      | He's planning to travel the entire country.                           |
     | 519130 | uig      | تاڭنىڭ ئالدىدا قاراڭغۇسى بار.                                          |
     | 519131 | epo      | Li trovas kritikindan en ĉio kion mi faras.                           |
     +--------+----------+-----------------------------------------------------------------------+

和

链接

     +------------+--------------+
     | sentenceId | translatedId |
     +------------+--------------+
     |     124665 |      1972285 |
     |     124666 |       279328 |
     |     124666 |       957578 |
     |     124667 |       279327 |
     |     124667 |       440607 |
     +------------+--------------+

它们由数百万行组成。我想在 sentenceId 之后向第二个表再添加两列，translatedId如下所示：

       +------------+-------------+--------------+-------------+
       | sentenceId | languageId1 | translatedId | languageId2 |
       +------------+-------------+--------------+-------------+

在上表中（sentenceId languageId1）和（translatedId languageId2）确实是成对的。 languageId1和languageId2分别显示sentenceId和translatedId的语言速记。但是在语言列的第一个表中定义的句子 Id 和翻译 Id 的语言简写。为了形成该表，必须在第一个表中查找每个sentenceId和翻译的 Id 值，并且必须在 languageId1 和 languageId2 中替换找到的语言速记。那么我该如何形成上面的表格呢？

Answer 1

这会

将您的结果添加到新表中（您需要在之前创建它）

INSERT INTO mynewtable 
SELECT LINKS.sentenceId, SRC_SENT.language AS srcLanguage,
       LINKS.translatedId, DST_SENT.language AS dstLanguage
FROM LINKS
INNER JOIN sentences SRC_SENT ON (LINKS.sentenceId = SRC_SENT.id)
INNER JOIN sentences DST_SENT ON (LINKS.translatedId = DST_SENT.id)

如果要向表链接添加列，则必须在之前修改其结构，然后更新它。