MySQL 如何将具有相同字段的两个表的结果连接到一个字段？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4304817/
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 join result from two tables with same field into one field?
提问by VeroLom
I have tables like this:
我有这样的表：
Table1 Table2
name1 | link_id name2 | link_id
text 1 text 2
text 2 text 4
And I wanna have result:
我想要结果：
name1 name2 link_id
text text 1
text text 2
text text 4
How I can do this?
我怎么能做到这一点？
ADD: Sry, my English in not good. I have device, device_model and device_type tables with duplicate field counter_set_id. I wanna select fields from counter_set with all values of counter_set_id. I need to fetch values only from counter_set_id fields
ADD：Sry，我的英语不好。我有带有重复字段 counter_set_id 的设备、设备模型和设备类型表。我想从 counter_set 中选择具有 counter_set_id 的所有值的字段。我只需要从 counter_set_id 字段中获取值
Now I have this query:
现在我有这个查询：
SELECT `dev`.`counter_set_id`, `mod`.`counter_set_id`, `type`.`counter_set_id`
FROM `device` AS `dev`
LEFT JOIN `device_model` AS `mod` ON `dev`.`device_model_id` = `mod`.`id`
LEFT JOIN `device_type` AS `type` ON `mod`.`device_type_id` = `type`.`id`
WHERE `dev`.`id` = 4;
This returns 3 columns but I need all values in one column
这将返回 3 列，但我需要一列中的所有值
This is final variant I think:
这是我认为的最终变体：
SELECT `dev`.`counter_set_id`
FROM `device` AS `dev` LEFT OUTER JOIN
`device_model` AS `mod` ON `dev`.`device_model_id` = `mod`.`id`
WHERE `dev`.`id` = 4 AND
`dev`.`counter_set_id` IS NOT NULL
UNION
SELECT `mod`.`counter_set_id`
FROM `device_model` AS `mod` LEFT OUTER JOIN
`device` AS `dev` ON `mod`.`id` = `dev`.`device_model_id`
WHERE `mod`.`counter_set_id` IS NOT NULL;
回答by Lynette Duffy
Based on the sample tables and desired output you provided, it sounds like you might want a FULL OUTER JOIN. Not all vendors implement this, but you can simulate it with a LEFT OUTER join and a UNION to an EXCEPTION join with the tables reversed like this:
根据您提供的示例表和所需的输出，听起来您可能需要 FULL OUTER JOIN。并非所有供应商都实现了这一点，但您可以使用 LEFT OUTER 连接和 UNION 到 EXCEPTION 连接来模拟它，表如下所示：
Select name1, name2, A.link_id
From table1 A Left Outer Join
table2 B on A.link_id = B.link_id
Union
Select name1, name2, link_id
From table2 C Exception Join
table1 D on C.link_id = D.link_id
then your output would be like this:
那么你的输出将是这样的：
NAME1 NAME2 LINK_ID
===== ===== =======
text <NULL> 1
text text 2
<NULL> text 4
回答by Patrick
At first i thought a join would work but now i'm not sure... Im thinking a union of some type.
起初我认为加入会起作用，但现在我不确定......我在想某种类型的联合。
in all honesty this is a bad design imo.
老实说，这是一个糟糕的设计imo。
select * from table1
union
select * from table2
回答by Justin Niessner
I'm assuming that you're joining on the link_idfield. A normal join would result in four rows being returned (two of which are identical). What you're really asking for isn't combining fields, but getting only the distinct rows.
我假设你加入了这个link_id领域。正常连接将导致返回四行（其中两行相同）。您真正要求的不是组合字段，而是仅获取不同的行。
Just use the distinctkeyword:
只需使用distinct关键字：
select distinct t1.name1, t2.name2, t1.link_id
from Table1 as t1
inner join Table2 as t2
on t1.link_id = t2.link_id
回答by IamIC
Select all the IDs into a temp table (however that works in MySQL - a CTE in SQL Server), and use that as your joining table.
将所有 ID 选择到临时表中（但它在 MySQL 中有效 - SQL Server 中的 CTE），并将其用作连接表。
回答by Richard Marskell - Drackir
If the three columns have the same value, just
如果三列具有相同的值，只需
SELECT `type`.`counter_set_id`
FROM ...
Or, better yet, if they have the same value, you can do:
或者，更好的是，如果它们具有相同的值，您可以执行以下操作：
SELECT `dev`.`counter_set_id`
FROM `device` AS `dev`
WHERE `dev`.`id` = 4;
If you want to concatenate them (put them all into the same field), use this:
如果要连接它们（将它们全部放入同一字段），请使用以下命令：
SELECT CONCAT(
CAST(`dev`.`counter_set_id` AS CHAR),
',',
CAST(`mod`.`counter_set_id` AS CHAR),
',',
CAST(`type`.`counter_set_id` AS CHAR))
FROM ...
回答by deepcell
If you do not want to repeat a field result, use GROUP BY link_idin the end of your query
如果您不想重复字段结果，请GROUP BY link_id在查询末尾使用
If you want to show only link_idfield then:
如果您只想显示link_id字段，则：
SELECT DISTINCT ta.link_id
FROM tblA AS ta
INNER JOIN tblB AS tb
ON ta.link_id = tb.link_id
Also look for CONCAT, CASTand other usefull funcs on mysql manual
I hope this help you.
还可以在 mysql 手册中查找CONCAT,CAST和其他有用的功能，希望对您有所帮助。
回答by chzbrgla
What value does link_id 4 have for name1? What value does link_id 1 have for name2?
link_id 4 对 name1 有什么值？link_id 1 对 name2 有什么值？
Try looking up the different JOIN-Types ..
尝试查找不同的 JOIN-Types ..
http://dev.mysql.com/doc/refman/5.0/en/join.html
http://dev.mysql.com/doc/refman/5.0/en/join.html
Maybe I misunderstand the question at hand - sorry then.
也许我误解了手头的问题 - 那么抱歉。
cheers
干杯
