MySQL 加入多个具有相同列名的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32061752/
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
Join multiple tables with same column name
提问by bendenfield
I have these tables in my MySQL database:
我的 MySQL 数据库中有这些表：
General table:
总表：
+----generalTable-----+
+---------------------+
| id | scenario | ... |
+----+----------+-----+
| 1 | facebook | ... |
| 2 | chief | ... |
| 3 | facebook | ... |
| 4 | chief | ... |
Facebook Table:
脸书表：
+----facebookTable-----+
+----------------------+
| id | expiresAt | ... |
+----+-----------+-----+
| 1 | 12345678 | ... |
| 3 | 45832458 | ... |
Chief Table:
主表：
+------chiefTable------+
+----------------------+
| id | expiresAt | ... |
+----+-----------+-----+
| 2 | 43547343 | ... |
| 4 | 23443355 | ... |
Basically, the general table holds some (obviously) general data. Based on the generalTable.scenario you can look up more details in the other two tables, which are in some columns familiar (expiresAt for example) but in others not.
基本上，通用表包含一些（显然）通用数据。基于generalTable.scenario，您可以在其他两个表中查找更多详细信息，这些表在某些列中很熟悉（例如expiresAt），但在其他列中则不然。
My question is, how to get the joined data of generalTable and the right detailed table in just one query.
我的问题是，如何在一个查询中获取generalTable 的连接数据和正确的详细表。
So, I would like a query like this:
所以，我想要这样的查询：
SELECT id, scenario, expiresAt
FROM generalTable
JOIN facebookTable
ON generalTable.id = facebookTable.id
JOIN chiefTable
ON generalTable.id = chiefTable.id
And an output like this:
和这样的输出：
| id | scenario | expiresAt |
+----+----------+-----------+
| 1 | facebook | 12345678 |
| 2 | chief | 43547343 |
| 3 | facebook | 45832458 |
| 4 | chief | 23443355 |
However, this doesn't work, because both facebookTable and chiefTable have ambiguous column name "expiresAt". For the ease of use I want to keep it that way. The result table should also only have one column "expiresAt" that is automatically filled with the right values from either facebookTable or chiefTable.
但是，这不起作用，因为 facebookTable 和 ChiefTable 都有不明确的列名“expiresAt”。为了便于使用，我想保持这种状态。结果表也应该只有一列“expiresAt”，该列会自动填充来自 facebookTable 或 ChiefTable 的正确值。
I have the feeling, that this is actually a very easy task, but it's been a long workday for me and you know the days when you think about a problem for so long and can't see the possibly easy solution. So, can you help me?
我有一种感觉，这实际上是一项非常简单的任务，但对我来说这是一个漫长的工作日，你知道你思考一个问题很长时间并且看不到可能简单的解决方案的日子。那么，你能帮我吗？
Also, sorry for the bad English
另外，抱歉英语不好
回答by mgrant
You might want to consider adding expiredAt to your general table, and removing it from the others, to remove duplication in the schema, and to make this particular query simpler.
您可能需要考虑将 expiredAt 添加到您的通用表中，并将其从其他表中删除，以删除模式中的重复项，并使此特定查询更简单。
If you need to stick with your current schema, you can use table aliases to resolve the name ambiguity, and use two joins and a union to create the result you are looking for:
如果您需要坚持使用当前模式，您可以使用表别名来解决名称歧义，并使用两个连接和一个联合来创建您要查找的结果：
SELECT g.id, g.scenario, f.expiresAt
FROM generalTable g
JOIN facebookTable f
ON g.id = f.id
UNION ALL
SELECT g.id, g.scenario, c.expiresAt
FROM generalTable g
JOIN chiefTable c
ON g.id = c.id;
The outer join approach mentioned in another answer would also solve the problem.
另一个答案中提到的外连接方法也可以解决问题。
回答by Uueerdo
One way you could accomplish it is with LEFT JOIN. In the result fields you can do something like this for common fields IF(fTbl.id IS NULL, cTbl.expiresAt, fTbl.expiresAt) AS expiresAt.
您可以完成它的一种方法是使用LEFT JOIN. 在结果字段中，您可以对常用字段执行类似操作IF(fTbl.id IS NULL, cTbl.expiresAt, fTbl.expiresAt) AS expiresAt。
