连接两个表时如何删除一个连接键 [英] How to drop one join key when joining two tables

问题描述

我有两个桌子.两者都有很多列.现在，我有一个共同的列，称为ID，可以加入该列.

I have two tables. Both have lot of columns. Now I have a common column called ID on which I would join.

现在，如果我简单地这样做，由于两个表中都存在此变量ID，

Now since this variable ID is present in both the tables if I do simply this

select a.*,b.*
from table_a as a
left join table_b as b on a.id=b.id

这将导致错误，因为id是重复的(在两个表中都存在并且都包含在两个表中).

This will give an error as id is duplicate (present in both the tables and getting included for both).

我不想在select语句中分别写下b的每一列.我有很多专栏，这很痛苦.是否可以像SAS数据合并语句一样重命名join语句本身中b的ID列?

I don't want to write down separately each column of b in the select statement. I have lots of columns and that is a pain. Can I rename the ID column of b in the join statement itself similar to SAS data merge statements?

我正在使用Postgres.

I am using Postgres.

推荐答案

Postgres不会为您提供重复输出列名称重复的错误，但有些客户端会这样做. (重复的名称也不是很有用.)

Postgres would not give you an error for duplicate output column names, but some clients do. (Duplicate names are also not very useful.)

无论哪种方式，都使用 USING 子句作为连接条件将两个连接列折叠为一个:

Either way, use the USING clause as join condition to fold the two join columns into one:

SELECT *
FROM  tbl_a  a
LEFT  JOIN tbl_b b USING (id);

当您联接同一表(自联接)时，将有更多重复的列名.该查询几乎没有任何意义.对于不同表，这开始变得有意义.就像您在问题中所说的那样，开头为:I have two tables ...

While you join the same table (self-join) there will be more duplicate column names. The query would make hardly any sense to begin with. This starts to make sense for different tables. Like you stated in your question to begin with: I have two tables ...

为避免所有重复的列名，必须在SELECT子句中明确列出它们-可能会处理列别名以使两个实例都使用不同的名称.

To avoid all duplicate column names, you have to list them in the SELECT clause explicitly - possibly dealing out column aliases to get both instances with different names.

或者您可以使用 NATURAL 连接-如果适合您无法解释的用例:

Or you can use a NATURAL join - if that fits your unexplained use case:

SELECT *
FROM  tbl_a  a
NATURAL LEFT JOIN tbl_b b;

这会连接到共享相同名称的所有列，并自动折叠它们-与在USING子句中列出所有常见列的名称完全相同.您需要了解可能的NULL值的规则...

This joins on all columns that share the same name and folds those automatically - exactly the same as listing all common column names in a USING clause. You need to be aware of rules for possible NULL values ...

手册中的详细信息.

这篇关于连接两个表时如何删除一个连接键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！