如何将来自不同列的多个表的结果合并? [英] 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 |
+--------+-------------+----------------+
我想获取与共享列的给定值匹配的所有结果.我可以使用多个选择语句来做到这一点:
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屋!