MySQL 在mySQL中，是否可以从两个表中选择并合并列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2913338/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
In mySQL, Is it possible to SELECT from two tables and merge the columns?
提问by Travis
If I have two tables in mysql that have similar columns...
如果我在 mysql 中有两个具有相似列的表...
TABLEA
id
name
somefield1
TABLEB
id
name
somefield1
somefield2
How do I structure a SELECT statement so that I can SELECT from both tables simultaneously, and have the result sets merged for the columns that are the same?
如何构建 SELECT 语句，以便我可以同时从两个表中进行 SELECT，并为相同的列合并结果集？
So for example, I am hoping to do something like...
例如，我希望做一些类似的事情......
SELECT name, somefield1 FROM TABLEA, TABLEB WHERE name="mooseburgers";
...and have the name, and somefield1 columns from both tables merged together in the result set.
...并具有名称和来自两个表的 somefield1 列在结果集中合并在一起。
Thank-you for your help!
感谢您的帮助！
Sample output appended because question unclear:
附加示例输出，因为问题不清楚：
I want the rows from table1 and the rows from table2 appended in the resultset. For example if the tables contain
我希望将 table1 中的行和 table2 中的行附加到结果集中。例如，如果表包含
TABLEA
id(1) name(zoot) somefield(suit)
TABLEB
id(1) name(zoot) somefield(flute)
The resultet would look like:
name | somefield1
zoot suit
zoot flute
采纳答案by paxdiablo
You can combine columns from both tables using (id,name) as the joining criteria with:
您可以使用 (id,name) 作为连接条件合并两个表中的列：
select
a.id as id,
a.name as name,
a.somefield1 || ' ' || b.somefied1 as somefield1
from tablea a, tableb b
where a.id = b.id
and a.name = b.name
and b.name = 'mooseburgers';
If you want to join on just the (id) and combine the name and somefield1 columns:
如果您只想加入 (id) 并结合 name 和 somefield1 列：
select
a.id as id,
a.name || ' ' || b.name as name,
a.somefield1 || ' ' || b.somefied1 as somefield1
from tablea a, tableb b
where a.id = b.id
and b.name = 'mooseburgers';
Although I have to admit this is a rather unusual way of doing things. I assume you have your reasons however :-)
虽然我不得不承认这是一种相当不寻常的做事方式。但是，我认为您有自己的理由:-)
If I've misunderstood your question and you just want a more conventional union of the two tables, use something like:
如果我误解了您的问题，而您只想要两个表的更传统的联合，请使用以下内容：
select id, name, somefield1, '' as somefield2 from tablea where name = 'mooseburgers'
union all
select id, name, somefield1, somefield2 from tableb where name = 'mooseburgers'
This won't combinerows but will instead just append the rows from the two queries. Use unionon its own if you want to remove duplicate rows but, if you're certain there are no duplicates or you don't want them removed, union allis often more efficient.
这不会合并行，而只会附加来自两个查询的行。使用union它自己的，如果你想删除重复的行，但是，如果您确定有没有重复，或者你不希望将其删除，union all往往是更有效的。
Based on your edit, the actual query would be:
根据您的编辑，实际查询将是：
select name, somefield1 from tablea where name = 'zoot'
union all
select name, somefield1 from tableb where name = 'zoot'
(or unionif you don't want duplicates where a.name==b.name=='zoot'and a.somefield1==b.somefield1).
（或者，union如果您不想重复 wherea.name==b.name=='zoot'和a.somefield1==b.somefield1）。
回答by RedFilter
回答by Kibbee
Depending on what you mean by merge, here's a possible solution.
根据您所说的合并，这里有一个可能的解决方案。
Select id,name,somefield1 from TableA
WHERE Name ='mooseburgers'
UNION
Select id,name,somefield1 from TableB
WHERE Name ='mooseburgers'
Will allow you to show results from both tables with the results merged into 1 table.
将允许您显示两个表中的结果，并将结果合并到 1 个表中。
回答by aioobe
Do you perhaps mean
你可能的意思是
SELECT tableA.id, tableA.name, tableA.somefield1
FROM tableA, tableB
WHERE tableA.name = tableB.name AND tableA.name="mooseburgers"
