如何合并来自具有不同列的多个表的结果? [英] How to combine results from multiple tables with different columns?

问题描述

我有几个表，它们的列数和类型不同，并且共有一个列.

I have several tables with different numbers and types of columns, and a single column in common.

+--------+---------+------------+-------------+
| person | beardID | beardStyle | beardLength |
+--------+---------+------------+-------------+

+--------+-------------+----------------+
| person | moustacheID | moustacheStyle |
+--------+-------------+----------------+

我想获取与共享列的给定值匹配的所有结果.我可以使用多个select语句来做到这一点:

I want to fetch all the results that match a given value of the shared column. I can do it using multiple select statements like this:

SELECT * FROM beards WHERE person = "bob"

和

SELECT * FROM moustaches WHERE person = "bob"

但是这需要多个mysql API调用，这似乎效率很低.我希望可以使用UNION ALL在单个API调用中获得所有结果，但是UNION要求表具有相同数量和相似类型的列.我可以编写一条SELECT语句，通过添加具有NULL值的列来手动填充每个表的结果，但是对于另一些表和更多列的表，该语句将很快变得难以管理.

But this requires multiple mysql API calls, which seems inefficient. I was hoping I could use UNION ALL to get all the results in a single API call, but UNION requires that the tables have the same number and similar type of columns. I could write a SELECT statement that would manually pad the results from each table by adding columns with NULL values, but that would quickly get unmanageable for a few more tables with a few more columns.

我正在寻找一个大致如下的结果集:

I'm looking for a result set roughly like this:

+--------+---------+------------+-------------+-------------+----------------+
| person | beardID | beardStyle | beardLength | moustacheID | moustacheStyle |
+--------+---------+------------+-------------+-------------+----------------+
| bob    | 1       | rasputin   | 1           |             |                |
+--------+---------+------------+-------------+-------------+----------------+
| bob    | 2       | samson     | 12          |             |                |
+--------+---------+------------+-------------+-------------+----------------+
| bob    |         |            |             | 1           | fu manchu      |
+--------+---------+------------+-------------+-------------+----------------+

是否有一种方法可以快速且可维护地实现这一目标?还是我最好为每个表运行一个单独的查询?

Is there a way to achieve this that's fast and maintainable? Or am I better off running a separate query for each table?

说明:

我不是在寻找笛卡尔积.我不希望胡须和胡须的每种组合都排成一排，我不想胡须和胡须的每一排都排成一排.

I'm not looking for a cartesian product. I don't want a row for every combination of beard-and-moustache, I want a row for every beard and a row for every moustache.

因此，如果有3个相匹配的胡须和2个相匹配的胡须，我应该得到5行，而不是6行.

So if there are 3 matching beards and 2 matching moustaches I should get 5 rows, not 6.

推荐答案

这应该可以正常工作:

SELECT * FROM `beards` b LEFT OUTER JOIN `mustaches` ON (0) WHERE  person = "bob"
UNION ALL
SELECT * FROM `beards` b RIGHT OUTER JOIN `mustaches` ON (0) WHERE  person = "bob"

您不必自己处理列.左右外部联接可以完成这项工作. 不幸的是，mysql没有完全连接.这就是为什么您必须通过工会这样做

you don't have to handle the columns by yourself. the left and right outer join do this job. unfortunately mysql doesn't have a full join. that's why you have to do it this way with a union

SELECT * FROM `customer` b LEFT OUTER JOIN `charges` ON (0) LEFT OUTER JOIN `day` ON (0)
UNION
SELECT * FROM `customer` b RIGHT OUTER JOIN `charges` ON (0) LEFT OUTER JOIN `day` ON (0)
UNION
SELECT * FROM `customer` b LEFT OUTER JOIN `charges` ON (0) RIGHT OUTER JOIN `day` ON (0)

这是我进行的本地测试

这篇关于如何合并来自具有不同列的多个表的结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！