MySQL 查找一行中的重复项

Question

我只是想问是否可以进行查询来查看两个表之间的所有重复项？我尝试了一些解决方案，但它返回重复的行而不是重复的项目。

例如，我有这两个表:

表_a

| A | B | C | D |

..........................................

| door | book | keys | shoe |

| door | bags | desk | keys |

| rice | fish | cake | shoe |

表_b

| A | B | C | D |

| tape | fans | robe | spec |

| keys | shoe | fans | room |

| hall | pops | door | disc |

所以，最终它只返回所有有重复项的项目。例如，

| door |

| keys |

| shoe |

.....

我尝试了一些查询，但仍然找不到解决方案。谢谢。

Answer 1

您可以通过取消数据透视并使用 group by 来实现此目的:

select col
from (select a as col from table_a union all
      select b from table_a union all
      select c from table_a union all
      select d from table_a union all
      select a from table_b union all
      select b from table_b union all
      select c from table_b union all
      select d from table_b
     ) t
group by col
having count(*) > 1;

如果表很大，有比使用union all更有效的逆透视方法。