使用 where 按顺序翻译内容 2 种语言(语言 = "enus" 或 i18n = "enus" )

Question

我有一个带子查询的查询来获取翻译后的文本，但where子句不起作用
始终返回文本菜单，因为它是数据库中的第一个菜单，即使其他语言也是如此

如何尊重查询中的指定顺序（language="enus"或i18n="enus）
首先ptbr否则enus

[翻译]

key | language | text
1   | enus     | foo in enus
1   | ptbr     | foo in ptbr
1   | it       | foo in it

[查询]

select
    A.*,
    ( select text from translate where key = A.key and (language = "ptbr" or i18n = "enus") limit 1 ) `translate`
from table as A
where A.id = 1

Answer 1

使用多个LEFT JOIN子句。

SELECT a.*, IFNULL(p.text, e.text) AS text
FROM table AS a
LEFT JOIN translate AS p ON p.key = a.key AND p.language = 'ptbr'
LEFT JOIN translate AS e ON e.key = a.key AND e.i18n = 'enus'

将执行您想要的操作的子查询是：

SELECT text
FROM (SELECT 1 AS priority, text 
      FROM translate
      WHERE key = A.key
      AND language = 'ptbr'
      UNION
      SELECT 2 AS priority, text
      FROM translate
      WHERE key = A.key
      AND i18n = 'enus') x
ORDER BY priority
LIMIT 1

或者你可以做：

SELECT text
FROM translate
WHERE key = A.key
AND (language = "ptbr" or i18n = "enus")
ORDER BY language = "ptbr" DESC
LIMIT 1

一般来说，如果希望LIMIT 1返回首选行，则需要使用ORDER BY来确保该行在结果中处于第一位。