带条件的mysql连接

Question

我有两张这种结构的桌子

categories
--------------------------------
id  | create
1   | 2012-12-01 12:00:00
2   | 2012-12-01 12:00:00

category_i18ns
--------------------------------
category_id     | lang  | title
1               | en    | home page
1               | de    | HomE PAGEa
1               | fa    | خانه
2               | en    | about

有些类别包含所有语言的数据，而另一些类别则没有。例如，类别id 2只有en翻译的标题。我想要有一个查询，首先检查检索标题的当前语言，然后如果不存在，则显示其他语言。

在这个例子中，我有一个用i18n数据获取当前类别数据的查询，de是默认语言。对于类别id 1，我们有de标题，但对于类别2，我们没有。所以我想要英文版本作为分类的标题

SELECT * FROM `mhf_swe_ndzhmju3_test`.`categories` AS `Category`
LEFT JOIN `mhf_swe_ndzhmju3_test`.`category_i18ns` AS `CategoryLocale` ON (
`CategoryLocale`.`category_id` = `Category`.`id`
AND `CategoryLocale`.`lang` = 'de')

对“de”语言的渴望

Category.id     CategoryLocale.title    CategoryLocale.lang
1               HomE PAGEa              de
2               about                   en

Answer 1

SELECT Category.id,
        COALESCE(a.lang, b.lang) lang,
        COALESCE(a.title, b.title) title
FROM   categories AS Category
      LEFT JOIN category_i18ns AS a
           ON a.category_id = Category.id AND a.lang = 'de'
      LEFT JOIN category_i18ns AS b
           ON b.category_id = Category.id AND b.lang = 'en'

• SQLFiddle Demo

Answer 2

如果你有一个偏好的顺序，左加入所有的语言，并将它们按照偏好的顺序合并：

SELECT Category.id,
    COALESCE(a.lang, b.lang, c.lang, d.lang) lang,
    COALESCE(a.title, b.title, c.title, d.title) title
FROM   categories AS Category
  LEFT JOIN category_i18ns AS a
       ON a.category_id = Category.id AND a.lang = 'de'
  LEFT JOIN category_i18ns AS b
       ON b.category_id = Category.id AND b.lang = 'en'
  LEFT JOIN category_i18ns AS c
       ON c.category_id = Category.id AND c.lang = 'fr'
  LEFT JOIN category_i18ns AS d
       ON d.category_id = Category.id AND d.lang = 'es';

如果您想从随机语言中获取文本，请使用以下查询：

SELECT Category.id,
        COALESCE(a.lang, b.lang) lang,
        COALESCE(a.title, b.title) title
FROM   categories AS Category
      LEFT JOIN category_i18ns AS a
           ON a.category_id = Category.id AND a.lang = 'de'
      LEFT JOIN category_i18ns AS b
           ON b.category_id = Category.id
GROUP BY Category.id

http://sqlfiddle.com/#!2/2beaf/5

Answer 3

好吧，这不是最好的解决方案，但我认为应该可以很好地工作：

SELECT * FROM categories c INNER JOIN category_i18ns t1 
ON c.id = t1.category_id AND t1.lang = 'de'

UNION 

SELECT * FROM categories c LEFT JOIN category_i18ns t2 
ON c.id = t2.category_id and t2.lang != 'de' 
WHERE t2.category_id not in
   (SELECT DISTINCT category_id FROM category_i18ns WHERE lang = 'de') 
GROUP BY c.id

SQL Fiddle Example