MySQL查询：从一个表中合并多个表

Question

我的MySQL表格如下：

word1  word2  count
a      c      1
a      d      2
a      e      3
a      f      4
b      c      5
b      d      6
b      g      7
b      h      8

"a"和"b"是用户输入 - 从表中选择*，其中word1='a'或word1='b' - 获得约10000行

我需要一个查询来获得：

当输入为"a"时，word1是列

当输入为"b"时，word1_是列

word2和word2_是同一列，因此可以忽略其中之一

我需要将下面的表与上面的表组合起来。例如这个查询：

select 
  t1.word1, t1.word2, t1.count, 
  t2.word1 as word1_, t2.word2 as word2_, t2.count as count_
from table t1
join table t2 on t1.word2 = t2.word2
where t1.word1 = 'a' and t2.word1 = 'b'

产生

word1   word2   count   word1_  word2_  count_  
a       c       1       b       c       5
a       d       2       b       d       6

我需要在未找到word2的情况下获取count=0。

word1  word2  count  word1_  word2_  count_
a      c      1      b       c       5
a      d      2      b       d       6
a      e      3      b       e       0
a      f      4      b       f       0
a      g      0      b       g       7
a      h      0      b       h       8

附言：表格有1100万行，索引设置在word1上。

再附言：提供的答案有效，但是查询需要20秒才能完成。我需要自己以编程的方式完成这个任务，以获得更好的性能。

- valerij vasilcenko

---

那么您的意思是**word1_，word2_和count_**是不同的列吗？那它们分别代表什么呢？请更清楚地说明这些名称是什么。 - Deepak Sharma

我已经编辑了我的问题。 - valerij vasilcenko

Answer 1

您需要进行FULL OUTER JOIN，但是MySQL中并不存在该操作。

您可以尝试以下方式实现：

select 
      t1.word1, t1.word2, t1.count, 
      coalesce(t2.word1, 'b') as word1_, t1.word2 as word2_, coalesce(t2.count, 0) as count_
from table1 t1
left join table1 t2 on t1.word2 = t2.word2 and t2.word1 = 'b'
where t1.word1 = 'a' 
union
select 
      coalesce(t2.word1, 'a'), t1.word2 , coalesce(t2.count, 0),
      t1.word1 as word1_, t1.word2 as word2_, t1.count

from table1 t1
left join table1 t2 on t1.word2 = t2.word2 and t2.word1='a'
where t1.word1 = 'b'

请查看SqlFiddle网站。

- Raphaël Althaus

Answer 2

你真的不需要任何 UNION 或 OUTER JOIN

SELECT 'a' word1
     , b.word2
     , max(CASE word1 WHEN 'a' THEN count ELSE 0 END) count
     , 'b' _word1
     , b.word2 _word2
     , max(CASE word1 WHEN 'b' THEN count ELSE 0 END) _count
FROM   words a
       INNER JOIN (SELECT DISTINCT word2
                   FROM   words
                   WHERE  word1 IN ('a', 'b')) b ON a.word2 = b.word2
GROUP BY b.word2
ORDER BY b.word2

演示：SQLFiddle
在演示中，我添加了一行，其中word1既不是'a'也不是'b'，如果您想要word1的值无论如何都获取word2的值，请删除子查询的WHERE条件。

- Serpiton

---

这个方法可行，但比Raphaël Althaus的答案慢。 - valerij vasilcenko

我在自己的系统上尝试了一下。Raphaël Althaus的答案用了20秒（对我来说太慢了）。你的答案花了几分钟以上，所以我不得不放弃并重新启动mysql。但无论如何，还是谢谢你的努力！ - valerij vasilcenko