我有一个MySQL表翻译。现在我正在搜索一个查询,该查询返回与给定语言列表匹配的第一个翻译:
假设一个字符串有4种翻译:
id lang translation
--------------------------------
greeting en hey!
greeting de Hallo.
greeting de_at Servus!
greeting de_ch Grüezi
根据给定语言列表查询的期望结果:
language list result
-----------------------
de,en Hallo.
en,de hey!
de_at,de,en Servus!
这在单个查询中可能吗?
SELECT COALESCE( de_at.translation, de.translation, en.translation )
FROM (SELECT 'greeting' AS id) AS id
LEFT JOIN translations AS de_at ON( de_at.lang = 'de_at' AND id.id = de_at.id )
LEFT JOIN translations AS de ON( de.lang = 'de' AND id.id = de.id )
LEFT JOIN translations AS en ON( en.lang = 'en' AND id.id = en.id )
试试这个:
SELECT * FROM translations t WHERE id = 'greeting' AND lang IN ('de_at', 'de', 'en') ORDER BY FIELD(lang, 'de_at', 'de', 'en') LIMIT 1