php 如何合并来自不同列的多个表的结果？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4027212/
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
How to combine results from multiple tables with different columns?
提问by Robert
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"
and
和
SELECT * FROM moustaches WHERE person = "bob"
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.
但这需要多次调用mysql API，显得效率低下。我希望我可以使用 UNION ALL 在单个 API 调用中获得所有结果，但 UNION 要求表具有相同数量和相似类型的列。我可以编写一个 SELECT 语句，通过添加具有 NULL 值的列来手动填充每个表的结果，但是对于具有更多列的更多表，这将很快变得无法管理。
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?
有没有一种方法可以快速且可维护地实现这一目标？还是我最好为每个表运行一个单独的查询？
Clarification:
澄清：
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.
我不是在寻找笛卡尔积。我不希望胡须和胡须的每种组合都排成一行，我希望每个胡须都排一排，每个小胡子都排一排。
So if there are 3 matching beards and 2 matching moustaches I should get 5 rows, not 6.
因此，如果有 3 个匹配的胡须和 2 个匹配的胡须，我应该得到 5 行，而不是 6 行。
采纳答案by ITroubs
this should be working fine:
这应该工作正常：
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"
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
您不必自己处理这些列。左外连接和右外连接完成这项工作。不幸的是，mysql 没有完全连接。这就是为什么你必须通过工会这样做
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)
this is a local test i made
这是我做的本地测试
回答by rsc
I think you would be better by making queries for data in each table.
我认为通过查询每个表中的数据会更好。
One of other possibilities is to concatenate data from all columns into one big string (you could choose some sign to separete column's values), then you should be able to use union all clause to combine results from each query - but then you will have to parse each row.. And data types will be lost.
另一种可能性是将所有列中的数据连接成一个大字符串（您可以选择一些符号来分隔列的值），然后您应该能够使用 union all 子句来组合每个查询的结果 - 但随后您将不得不解析每一行......并且数据类型将丢失。
回答by brumScouse
Join on person....
加入个人....
I.e.
IE
Select t1.(asterix), t2.(asterix) FROM beards t1 INNER JOIN moustaches t2 On t2.person = t1.person
选择 t1.(asterix), t2.(asterix) FROM beards t1 INNER JOIN mustaches t2 On t2.person = t1.person
回答by Petah
SELECT *
FROM beards
JOIN moustaches
ON moustaches.person = beards.person
WHERE person = "bob"
回答by Vinnie
I had fun with this, not sure it's entirely manageable with what more you have to add, but it accomplished the goal.
我对此很感兴趣，不确定它是否完全可以通过添加更多内容来管理，但它实现了目标。
create table beard (
person varchar(20)
,beardID int
,beardStyle varchar(20)
,beardLength int )
create table moustache(
person varchar(20)
,moustacheID int
,moustacheStyle varchar(20))
insert into beard
select 'bob', 1, 'rasputin', 1
union select 'bob', 2, 'samson', 12
insert into moustache
select 'bob', 1, 'fu manchu'
declare @facialhair table (
person varchar(20)
,beardID int
,beardStyle varchar(20)
,beardLength int
,moustacheID int
,moustacheStyle varchar(20))
declare @i int
declare @name varchar(20)
set @name = 'bob'
set @i = (select COUNT(*) from beard where person = @name)
+ (select COUNT(*) from moustache where person = @name)
print @i
while @i > 0
begin
insert into @facialhair (person, beardID, beardStyle, beardLength)
select person, beardID, beardStyle, beardLength
from beard
where person = @name
set @i = @i-@@ROWCOUNT
insert into @facialhair (person, moustacheID, moustacheStyle)
select person, moustacheID, moustacheStyle
from moustache
where person = @name
set @i = @i-@@ROWCOUNT
end
select *
from @facialhair
