将一个表与另一表中的许多行连接起来

Question

我有一个本地化表，其中存储 MySQL 数据库的所有本地化内容。我正在尝试编写一个查询，对于具有多个列的给定表，该查询将检索这些列的所有本地化内容。用一个例子可能更容易理解：

foo

code    title    localized_title_code    description    localized_description_code
A001    Bar1     foo_A001_title          Baz1           foo_A001_description
A002    Bar2     foo_A002_title          Baz2           foo_A002_description

本地化

id    code                  value    locale
1     foo_A001_title        Bar1o    es
2     foo_A002_title        Bar2o    es
3     foo_A001_description  Baz1o    es
4     foo_A002_description  Baz2o    es

我想要这样的东西：

code title localized_title description localized_description
A001 Bar1  Bar1o           Baz1        Baz1o
A002 Bar2  Bar2o           Baz2        Baz2o

我知道我可以通过两个查询轻松地得到这个，例如：

select foo.title,//Or description
    localization.value as localized_title
from foo
join localization
where localization.code = concat('foo', foo.code, 'title')
and localization.locale = 'es';

但是有没有办法将两个本地化列放入一个结果中？可以安全地假设不会丢失条目（即，如果有标题，就有描述）。

Answer 1

这可能对你有用：

select foo.code,
    foo.title,
    title.value as localized_title,
    foo.description,
    description.value as localized_description
from foo
join localization as title on title.code = concat('foo', foo.code, 'title')
    and title.locale = 'es'
join localization as description on description.code = concat('foo', foo.code, 'description')
    and description.locale = 'es'