MySQL:按值优先级获取列 [英] MySQL: Getting columns by Value priorities

问题描述

我正在研究mySQL问题.我想设置一个翻译系统，在其中将键(占位符)保存在tblkey中，将翻译存储在tbltranslations中.

I am working on a mySQL issue. I want to setup a translation system, where keys (placeholders) are saved in the tblkeys and the translations are stored in tbltranslations.

系统中的每个用户都有语言设置，并且当用户无法使用该语言进行翻译时，我希望系统使用英语.

Every user in the system has a language setting and I want the system to use English, when the translation in the user's language is not available.

SELECT * FROM tblkeys, tbltranslations 
WHERE
tblkeys.fiproject = 1
AND tbltranslations.fiproject = tblkeys.fiproject
AND tbltranslations.fikey = tblkeys.idkey
AND (tbltranslations.dtlanguage = 'de' OR tbltranslations.dtlanguage = 'en')
GROUP BY tblkeys.idkey

在这里，我使用了一个简单的OR来说明我的意思.问题是，我希望系统首选 de ，但如果无法使用，请使用 en 作为语言.

Here I used a simply OR for demonstrating what I mean. The problem, I want the system to prefer de, but if not available use en as dtlanguage.

我该如何处理?

先谢谢！

推荐答案

您可以使用两个左联接.一种连接所有DE字符串，一种连接所有EN字符串，然后在您的选择中添加IFNULL(de.text，en.text)

You can use two left joins. One joining all DE strings, one for joining all EN strings and then add an IFNULL(de.text, en.text) in your select

selectr tblkeys.idkey, ifnull(de.translation, en.translation)
from tblkeys
left join 
(select idkey, translation from tbltranslations where tbltranslations.dtlanguage = 'de') de 
on de.idkey = tblkeys.idkey
left join
(select idkey, translation from tbltranslations where tbltranslations.dtlanguage = 'en') en
on en.idkey = tblkeys.idkey

这篇关于MySQL:按值优先级获取列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！