mysql - 按一个参数选择值并用其他参数完成表格

Question

真的很难在标题中解释我的问题。基本上我有一个包含多种语言的翻译表，看起来像这样

+---------------+----------------------------------+----------+
| name          | translation                      | language |
+---------------+----------------------------------+----------+
| translation_1 | This is text for translation n.1 | en       |
+---------------+----------------------------------+----------+
| translation_2 | This is text for translation n.2 | en       |
+---------------+----------------------------------+----------+
| translation_3 | This is text for translation n.2 | en       |
+---------------+----------------------------------+----------+
| translation_2 | Toto je text pre preklad č.2     | sk       |
+---------------+----------------------------------+----------+

现在我需要获取语言“sk”的翻译，但我不能确定所有语言的所有翻译总是存在，所以我想要实现的是获取语言“sk”的所有内容并完成翻译语言“en”(这将是默认语言，并且始终具有所有翻译)。

这应该是结果

+---------------+----------------------------------+----------+
| name          | translation                      | language |
+---------------+----------------------------------+----------+
| translation_1 | This is text for translation n.1 | en       |
+---------------+----------------------------------+----------+
| translation_2 | Toto je text pre preklad č.2     | sk       |
+---------------+----------------------------------+----------+
| translation_3 | This is text for translation n.2 | en       |
+---------------+----------------------------------+----------+

多谢指教!

Answer 1

考虑到“en”的每个翻译都存在，你可以这样做

SELECT
  en.name,
  IFNULL(sk.translation, en.translation) AS translation,
  IFNULL(sk.language, en.language) AS language
FROM translation AS en
  LEFT JOIN translation AS sk ON en.name = sk.name AND sk.language = "sk"
WHERE en.language = "en"

您选择所有英语翻译并与瑞典语翻译进行左连接。对于翻译和语言列，您声明喜欢瑞典语，但不喜欢英语。