连接具有相同列名的多个表 [英] Join multiple tables with same column name

问题描述

我的MySQL数据库中有这些表:

I have these tables in my MySQL database:

常规表:

+----generalTable-----+
+---------------------+
| id | scenario | ... |
+----+----------+-----+
| 1  | facebook | ... |
| 2  | chief    | ... |
| 3  | facebook | ... |
| 4  | chief    | ... |

Facebook表格:

Facebook Table:

+----facebookTable-----+
+----------------------+
| id | expiresAt | ... |
+----+-----------+-----+
| 1  | 12345678  | ... |
| 3  | 45832458  | ... |

首席表:

+------chiefTable------+
+----------------------+
| id | expiresAt | ... |
+----+-----------+-----+
| 2  | 43547343  | ... |
| 4  | 23443355  | ... |

基本上，常规表包含一些(显然是 )常规数据.基于generalTable.scenario，您可以在其他两个表中查找更多详细信息，这些表在某些熟悉的列中(例如，expiresAt)，而在其他一些列中则不然.

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和正确的明细表的联接数据.

所以，我想要这样的查询:

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

和这样的输出:

| id | scenario | expiresAt |
+----+----------+-----------+
| 1  | facebook | 12345678  |
| 2  | chief    | 43547343  |
| 3  | facebook | 45832458  |
| 4  | chief    | 23443355  |

但是，这是行不通的，因为facebookTable和ChiefTable都有不明确的列名"expiresAt".为了易于使用，我想保持这种方式.结果表还应该只包含一列"expiresAt"，该列会自动用facebookTable或ChiefTable中的正确值填充.

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.

我觉得这实际上是一个非常容易的任务，但是对我来说这是一个漫长的工作日，并且您知道自己考虑问题的时间太长了，无法找到可能的简单解决方案.所以，你能帮我吗?

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

推荐答案

您可能要考虑将expiredAt添加到常规表中，并将其从其他表中删除，以删除架构中的重复项，并使此特定查询更简单.

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.

如果需要坚持使用当前架构，则可以使用表别名来解决名称歧义性，并使用两个联接和一个并集来创建所需的结果:

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.

这篇关于连接具有相同列名的多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！