MySQL 条件 GROUP_BY

Question

我的数据库看起来(有点)像这样:

Table 'posts':
ID  title             text
62  Trees in Europe   You can find fine trees in european woods.
63  Animals in Europe The european fauna is mostly harmless.

Table 'translations':
ID  reference_id  reference_field  translation
36  62            title            Bäume in Europa
37  62            text             Da sind viele Bäume in Europas Wäldern .
44  63            text             Die meisten europäischen Tiere sind harmlos.
47  63            title            Tiere in Europa

translations 表中的 reference_field 给出了翻译文本将在 posts 表的哪个字段中显示的信息。

我想要一个包含一行的 SELECT 作为结果，它给我相应文本的串联，即

ID  title                                  text
62  Trees in Europe // Bäume in Europa     You can find fine trees in european woods. // Du kannst in Europas Wäldern viele Bäume finden.
63  Animals in Europe // Tiere in Europa   The european fauna is mostly harmless. // Die meisten europäischen Tiere sind harmlos.

我试了很多，还是没搞定。我遇到的问题是 translation 列的引用在每一行中都发生了变化。我得到的最接近的结果是 一个 列被正确连接:

SELECT a.id, 
IF (t.reference_field LIKE "title", CONCAT(left(a.title,20), ' // ', LEFT(t.value, 20)), LEFT(a.title, 20)) AS title, 
IF (t.reference_field LIKE "text", CONCAT(left(a.text,20), ' // ', LEFT(t.value, 20)), LEFT(a.text, 20)) AS summary, 
t.reference_field
FROM posts AS a 
JOIN translations AS t on a.id = t.reference_id 
WHERE a.id=62 
AND t.reference_field IN ('introtext', 'title') 
GROUP BY a.id;

我必须如何修改 SQL 才能给出预期的结果？

感谢任何帮助或提示!谢谢!

Answer 1

我建议如下:

SELECT p.ID
    ,concat(p.title, ' // ', t_title.translation) AS title
    ,concat(p.text, ' // ', t_text.translation) AS text
FROM posts AS p
LEFT JOIN translations AS t_title ON p.id = t_title.reference_id
    AND t_title.reference_field = 'title'
LEFT JOIN translations AS t_text ON p.id = t_text.reference_id
    AND t_text.reference_field = 'text'

SQL Fiddle 演示:http://sqlfiddle.com/#!9/5ad47d/4/0

一点解释:当您多次拥有相同的 reference_id 并且您正在连接这两个表时，您将获得比您预期更多的行。所以需要过滤。这就是我使用 reference_field 并加入 2 倍时间的原因