MySQL通过键选择行或回退以默认键选择 [英] MySQL Select rows by a key or fall back to select by default key

问题描述

我在MySQL中有以下语言表，用于选择其他语言的文本.

I have the following language table in MySQL to select text in different languages.

CREATE TABLE `lang` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`group` INT(10) UNSIGNED NOT NULL,
`text` VARCHAR(255) NULL DEFAULT NULL,
`language` VARCHAR(10) NOT NULL DEFAULT 'def',
PRIMARY KEY (`id`),
UNIQUE INDEX `group_language` (`group`, `language`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT

该表包含以下数据

id  group  text               language
1   1      This is English    def
2   2      Helo sir           def
3   3      how are you?       def
4   3      Wie geht es dir?   de

group字段告诉我每种翻译属于哪些文本. 在上面的示例中，组"3"具有默认文本(英语)和德语翻译.

The group field tells me what texts belong together for each translation. In the above example group '3' has a default text (English) and a German translation.

现在，我想选择德语的所有文本，如果不存在，我将为此保留后备英语文本.

Now I want to select all texts for German and if they don't exist, I will want to have the fallback English text for that.

有人知道如何将它们整合到SQL语句中吗?

Anybody an idea how I can put that together into a SQL statement?

推荐答案

SELECT  DISTINCT
        COALESCE(b.ID, a.ID) ID,
        COALESCE(b.`GROUP`, a.`GROUP`) `GROUP`,
        COALESCE(b.`text`, a.`text`) `text`,
        COALESCE(b.language, a.language) language
FROM   TableName a
       LEFT JOIN
        (
            SELECT ID, `GROUP`, `text`, language
            FROM tableName
            WHERE language = 'de'
         ) b ON a.ID <> b.ID AND a.`GROUP` = b.`GROUP`

• SQLFiddle演示
• SQLFiddle Demo

这篇关于MySQL通过键选择行或回退以默认键选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！