mySQL不同的联接? [英] mySQL Distinct Join?

问题描述

我具有以下数据库结构

table_1
  text_1 (INT)
  text_2 (INT)


i18n
  id_i18n (PK INT)
  locale  (PK VARCHAR(5))
  text    (TEXT)

在表_1上，列text_1和text_2是指向某些i18n.id_i18n条目的外键. 我可以轻松地加入特定语言环境的条目

On table_1, the columns text_1 and text_2 are foreign keys pointing to some i18n.id_i18n entries. I can easily join the entries for a specific locale

SELECT t1.text as text_1, t2.text as text_2
FROM table_1
LEFT JOIN i18n as t1 ON text_1 = t1.id_i18n and t1.locale = "en_us"
LEFT JOIN i18n as t2 ON text_2 = t2.id_i18n and t2.locale = "en_us"

我还可以得到以下信息

row1: locale, text_1, text_2
row2: locale, text_1, text_2
row3: locale, text_1, null
row4: locale, text_1, text_2

使用此查询

SELECT t1.text as text_1, t2.text as text_2
FROM room
LEFT JOIN i18n as t1 ON text_1 = t1.id_i18n
LEFT JOIN i18n as t2 ON text_2 = t2.id_i18n and t1.locale = t2.locale
group by t1.locale;

假设我有以下i18n条目

assuming the I have the following i18n entries

      id_i18n   locale  text
row1: 1         en_us   text_1_for[en_us]
row2: 1         en_gb   text_1_for[en_gb]
row3: 1         el_gr   text_1_for[el_gr]
row4: 2         en_us   text_2_for[en_us]
row5: 2         en_gb   text_2_for[en_gb]
row6: 2         pr_pk   text_2_for[pr_pk]
row7: 1         en_ca   text_1_for[en_ca]

然后是一个链接到名为tbl的表的表

and then a table that links to those named tbl

     id, i18n_text_1, i18n_text_2
row1 1   1            2

我想产生一个像这样的结果集

I want to produce a result set like

      locale,  text_1,              text_2
row1: en_us    text_1_for[en_us]    text_2_for[en_us]
row2: en_gb    text_1_for[en_gb]    text_2_for[en_gb]
row3: el_gr    text_1_for[el_gr]    null
row4: pr_pk    null                 text_2_for[pr_pk]
row5: en_ca    text_1_for[en_ca]    null

希望这会有所帮助:)

推荐答案

尝试一下:

SELECT     a.locale, c.text AS text_1, d.text AS text_2
FROM       (SELECT DISTINCT locale FROM i18n) a
CROSS JOIN table_1 b
LEFT JOIN  i18n c ON b.text_1 = c.id_i18n AND a.locale = c.locale
LEFT JOIN  i18n d ON b.text_2 = d.id_i18n AND a.locale = d.locale

SQLFiddle演示

---

编辑:这可能会更好:

SQLFiddle Demo

---

EDIT: This might work better:

SELECT
    a.locale, b.text_1, c.text_2
FROM
    (SELECT DISTINCT locale FROM i18n) a
LEFT JOIN
(
    SELECT b.locale, b.text AS text_1
    FROM   table_1 a
    JOIN   i18n b ON a.text_1 = b.id_i18n
) b ON a.locale = b.locale
LEFT JOIN
(
    SELECT b.locale, b.text AS text_2
    FROM   table_1 a
    JOIN   i18n b ON a.text_2 = b.id_i18n
) c ON a.locale = c.locale

这篇关于mySQL不同的联接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！