SQL联接按条件返回所有行(在表中或同时在表中)

Question

我有一个表，其中有一列对内容进行分类：

id  lang   string
1   EN     text1_en
1   DE     text1_de
1   FR     text1_fr
2   EN     text2_en
2   DE     text2_de
3   DE     text3_de
3   FR     text3_fr

我想获得这样的结果：

id  lang  string     id  lang  string    id  lang  string
1   EN    text1_en   1   DE    text1_de  1   FR    text1_fr
2   EN    text2_en   2   DE    text2_de  NULL NULL NULL
NULL NULL NULL       3   DE    text3_de  3   FR    text3_fr

我一开始尝试加入：

select 
  c1.id,c1.lang,c1.string,
  c2.id,c2.lang,c2.string,
  c3.id,c3.lang,c3.string
from 
  mytable c1
  left join mytable c2 on (c1.id=c2.id and c2.lang='DE')
  left join mytable c3 on (c1.id=c3.id and c3.lang='FR')
where
  c1.lang='EN'
order by c1.id

然而，我只得到id为1和2的结果。

如果我将c1.lang='EN'移动到on条件，我将获得前3列（不仅仅是lang='EN'）的所有表行

Answer 1

您可以在mysql以外的服务器中使用完全外部联接。对于大型表，此查询将花费一些时间。

  select 
      c1.id,c1.lang,c1.string,
      c2.id,c2.lang,c2.string,
      c3.id,c3.lang,c3.string
    from 
      mytable c1 left  join mytable c2 on (c1.id=c2.id and c2.lang='DE')   
      left join mytable c3 on (c1.id=c3.id and c3.lang='FR')  
    where
      c1.lang='EN'
    UNION ALL 
    select 
      c1.id,c1.lang,c1.string,
      c2.id,c2.lang,c2.string,
      c3.id,c3.lang,c3.string
    from 
      mytable c2 left  join mytable c1 on (c2.id=c1.id and c1.lang='EN')   
      left join mytable c3 on (c2.id=c3.id and c3.lang='FR')  
    where
      c2.lang='DE'
    UNION ALL 
    select 
      c1.id,c1.lang,c1.string,
      c2.id,c2.lang,c2.string,
      c3.id,c3.lang,c3.string
    from 
      mytable c3 left  join mytable c1 on (c3.id=c1.id and c1.lang='EN')   
      left join mytable c2 on (c3.id=c2.id and c2.lang='DE')  
    where
      c3.lang='FR'