SQL 内连接两个具有相同列名的表 [英] SQL inner join two tables with the same column names

问题描述

我有两张表，列数不等.(我不知道有多少列或名称是什么)例如表 A 和表 B.

I have two tables with a variable amount of columns. (I don't know how many columns or what there names will be) for example Table A and Table B.

表A:

ID | B_ID | {variable} 

表B

ID | {variable} 

查询:

SELECT TableA.*, TableB.* FROM TableA INNER JOIN TableB ON TableA.B_ID= TableB.id;

当 TableA 和 TableB 都有一个同名的列时，我无法区分这两个不同的列.例如，两个表都有列名称"，此查询将导致:

When TableA and TableB both have a column with a same name I can't distinguish between the two different columns. For example of both tables has the column "Name" this query would result in :

ID | ID | B_ID | NAME | NAME |
 1 | 35 | 35   | bob  | jim  |

我正在寻找的是一种区分两个表的方法.最好带有列名的前缀，例如.

What I am looking for is a way to differentiate between the two tables. Preferably with a prefex for the column names such as.

TableA_ID | TableB_ID | TableA_B_ID | TableA_NAME | TableB_NAME |
        1 |        35 |          35 |         bob |         jim |

我知道AS"关键字，但问题是我事先不知道列名是什么.(我不知道 TableA 或 TableB 是否会有列名称)

I know of the "AS" keyword but the problem is that I don't know what the column names are going to be before hand. (I don't know if TableA or TableB are going to have the column Name)

所以我的问题是

当两个表可能具有相同的列名时，如何使用 INNER JOIN 区分两个表之间的列?

How do you differentiate the columns between the two tables with a INNER JOIN when the tables may have the same column names ?

我正在使用 SQLite3.

I am using SQLite3.

推荐答案

您的结果集(给定您的查询)应该包含所有 TableA 列，然后是所有 TableB 列，因此当您到达第二个 ID 时 列，您知道您正在研究 TableB 数据.

Your result set (given your query) should have all of the TableA columns followed by all the TableB colums, so when you get to the second ID colum, you know you're into the TableB data.

也就是说，您从两个表中查询所有数据，而您在功能上一无所知...

That said, it is would seem odd to me that you're querying all the data out of two tables about which you know functionally nothing...

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