代码之家 › 专栏 › 技术社区 › Ice76

将联接表联接到自身

join mysql

-1

Ice76 · 技术社区 · 7 年前

这些列是:

id
product_id
key
translation
language_id

另一个表dictionary\u versions只有dictionary\u id和翻译版本

id
dictionary_id
version_id

表中每个键有两种不同的翻译,但有时只有一种翻译。我正在寻找一种方法来获得一种语言的所有翻译,如果有的话,另一种语言id的翻译。

问题是,当两种语言有10000种翻译时,速度会变慢。使用dictionary\u versions表作为两者的where子句,有没有更好的方法将表连接到自身上?

SELECT
    d.*
FROM
    dictionary d 
LEFT JOIN
    dictionary_versions dv ON dv.dictionary_id = d.id
LEFT JOIN
    dictionary d2
LEFT JOIN
    dictionary_versions dv2 ON d2.id = dv2.dictionary_id
ON
    d2.key = d.key 
WHERE
    d.product_id = 1 AND dv.version_id = 3
AND
    d.language_id = 1
LIMIT
    0,10

我试过的另一种方法,是用临时桌子,这很管用,但很慢。它通过两个查询完成:

CREATE TEMPORARY TABLE IF NOT EXISTS dictionary_temp_1 
AS (
    SELECT d.* FROM `dictionary` AS `d`
    LEFT JOIN `dictionary_versions` AS `dv` ON dv.dictionary_id = d.id
    WHERE d.product_id = 1 AND dv.version_id = 3 AND d.language_id = 1 
    ORDER BY fd.key ASC LIMIT 0,10 )

SELECT
    d.key,
    d2.key AS toKey,
    d.translation AS `1`,
    d2.translation AS `2`
FROM
    `dictionary` AS `d`
LEFT JOIN
    `dictionary_versions` AS `dv` ON d.id = dv.dictionary_id
LEFT JOIN
    `dictionary_temp_1` AS `d2` ON d2.key = d.key
WHERE
     d.product_id = 1 AND dv.version_id = 3 AND d.language_id = 1
ORDER BY
     d.key ASC LIMIT 0,10

1 回复 | 直到 7 年前

1

0

Ice76 7 年前

多亏@philipxy把我指向 this answer 供参考。

SELECT d.key, d2.key AS toKey, d.translation AS `1`, d2.translation AS `2`
FROM dictionary d 
INNER JOIN dictionary_versions dv ON dv.dictionary_id = d.id 
LEFT JOIN (
    SELECT d.* 
    FROM `dictionary` AS `d`
    INNER JOIN `dictionary_versions` AS `dv` ON d.id = dv.dictionary_id 
    WHERE d.product_id = 1 AND dv.version_id = 3 AND d.language_id = 2
) d2 ON d2.key = d.key 
WHERE d.product_id = 1 AND dv.version_id = 3 AND d.language_id = 1