mysql查询组合表从一个表。

My mysql table looks like this:

我的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" and "b" are user inputs - select * from table where word1='a' or word1='b' - gets ~10000 rows

“a”和“b”是用户输入——从word1='a'或word1='b'得到~10000行的表中选择*

I need a query to get: word1 is column for intput "a"

我需要一个查询来获取:word1是intput“a”的列

word1_ is column for input "b"

word1_是输入“b”的列

word2 and word2_ are the same column so one of them can be ignored

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

i need to combine table below from table above. for example this query:

我需要将下表和上表合并。例如这个查询:

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'

produces

生产

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

I need to get count=0 where word2 is not found.

我需要在没有找到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

P.S. the table has 11million rows index is set on word1

表中有1100万行索引是在word1上设置的。

P.P.S. Provided answer does work but it took 20 sec to complete the query. I will need to do this programmatically by myself to get better performance.

P.P.S.提供的答案确实有效，但完成查询需要20秒。为了获得更好的性能，我需要通过编程实现这一点。

2 个解决方案

#1

You need a FULL OUTER JOIN... which doesn't exist in mysql.

你需要一个完整的外部连接……这在mysql中是不存在的。

You can do it this way.

你可以这样做。

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'

see SqlFiddle

看到SqlFiddle

#2

You don't really need any UNION or 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

Demo: SQLFiddle
In the demo I added a row where word1 is neither 'a' or 'b', if you want every value of word2 regardless the value of word1 just strip the WHERE condition of the subquery

在Demo中，我添加了一行，其中word1既不是“a”也不是“b”，如果你想要word2的每个值，不管word1的值是多少，只要去掉子查询的where条件

#1