mysql 中的回退键,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

The table contains the following data

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

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.

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`