mysql select作为返回字段_mysql – 设置select中返回的空字段的默认值

给出两个表：

image(id, cat_id, name)

image_translation(id, title, desc, lang)

(名称和ID是唯一的)

查询以选择特定文件的所有翻译：

SELECT c.id AS c__id,

c.cat_id AS c__cat_id,

c.name AS c__name,

c2.id AS c2__id,

c2.title AS c2__title,

c2.desc AS c2__desc,

c2.lang AS c2__lang,

FROM image c

LEFT JOIN image_translation c2

ON c.id = c2.id

WHERE ( c1.name = 'file.jpg' )

收益：

c__id | c__cat_id | c2__id | c__name | c2__title | cs__desc | c2__lang

------+------------+-----------+-----------+---------------+--------------+---------

114 | 2 | 114 | file.jpg | default title | default desc | en

114 | 2 | 114 | file.jpg | NULL | desc in de | de

114 | 2 | 114 | file.jpg | title in fr | '' | fr

要选择特定语言的特定文件的翻译,

显然我需要添加where子句.

但随后它返回默认行内容,其中可能包含翻译中的空字段.

如何将结果与默认值合并,并使用默认值覆盖empy字段？为lang en返回默认值：

SELECT c2.title AS c2__title,

c2.desc AS c2__desc,

c2.lang AS c2__lang,

FROM image c

LEFT JOIN image_translation c2

ON c.id = c2.id

WHERE ( c1.name = 'file.jpg' )

AND WHERE ( c2.lang = 'en')

我期望的样本结果：

对于德语：

-- AND WHERE ( c2.lang = 'de' )

c__id | c__cat_id | c2__id | c__name | c2__title | cs__desc | c2__lang

-----+------------+-----------+-----------+---------------+--------------+---------

114 | 2 | 114 | file.jpg | default title | desc in de | de

对于法语：

-- AND WHERE ( c2.lang = 'fr' )

c__id | c__cat_id | c2__id | c__name | c2__title | cs__desc | c2__lang

-----+------------+-----------+-----------+---------------+--------------+---------

114 | 2 | 114 | file.jpg | title in fr | default desc | fr

如何在一个智能查询中正确执行？

(我使用MySQL和Doctrine ORM与I18N行为)

解决方法:

我不知道你是否要求这个简单的查询：

对于’fr’：

SELECT c2.title AS c2__title,

coalesce( nullif( c2.desc, '') , c3.desc ) AS desc,

coalesce( nullif( c2.lang, '') , c3.lang ) AS lang,

FROM image c

LEFT JOIN image_translation c2

ON c2.lang = 'fr' and c.id = c2.id

LEFT JOIN image_translation c3

ON c3.lang = 'en' and c.id = c3.id

WHERE ( c1.name = 'file.jpg' )

标签：sql,mysql,postgresql,select

来源： https://codeday.me/bug/20190826/1725775.html