在SQL中将表联接到自身并保存结果 [英] Joining a table onto itself in SQL and saving the result

问题描述

在SQL中，我将表联接到自身上:

In SQL, I am joining a table onto itself:

SELECT * FROM table AS a
LEFT JOIN table AS b
ON a.NAME = b.NAME

因此，它将获取出现在另一张表的一行中的具有相同NAME的所有行(也将返回同一行两次！).

So it's fetching all the rows which have the same NAME appearing in a row of the other table (will also return the same row twice!).

假设我要将结果保存到临时表中，例如:

Let's say that I want to save the result into a temporary table, say something like:

SELECT * INTO ##temp_table FROM (
    SELECT * FROM table AS a
    LEFT JOIN table AS b
    ON a.NAME = b.NAME
)

哦，亲爱的. SQL说:

Oh dear. SQL says:

The column 'NAME' was specified multiple times.

原因:只有每个列名都是唯一的，SQL才能创建一个表.

Reason: SQL can only create a table if every column name is unique.

一种明显的解决方案:为第二个表"中的每个列名都赋予一个别名.

Obvious solution: give every column name in the "second table" an alias.

我的问题是，我正在使用的实际表大约有40列.给这些列中的每一列加上别名似乎是浪费时间.当然，我不需要每一个列，因此可以删除其中的一些，但是确定我现在需要的列似乎也很浪费.

My problem is that the actual tables I'm working with have about 40 columns. Giving every one of those columns an alias seems like a wasteful use of time. Sure, I don't need every column so could drop some of them, but deciding which ones I require just now also seems wasteful.

问题:是否有一种简便的方法来重命名每列?例如，是否可以在每个列名称后附加_2或_a?

Question: is there a shorthand way to rename every column? For example, can I append every column name with a _2 or an _a?

推荐答案

好吧，您有一个查询，有2个联接表，将返回两个表列(我不在乎您是否将同一个表与其自身联接)

Ok, you have a query, with 2 joined tables, wich returns both tables columns (i don't care if you are joining the same table with itself).

所以您有两个可能的结果

So you have two possible results

显示两个具有不同别名(AS)的列

Show both colums, with differents alias (AS)

SELECT * INTO ##temp_table FROM (
    SELECT a.Name AS NameA, b.Name AS NameB FROM table AS a
    LEFT JOIN table AS b
    ON a.NAME = b.NAME
)

或者，如果您不希望它们重复(因为另一个将返回相同名称的两倍)

Or, if you don't want them duplicated (because the other will return two times the same name)

SELECT * INTO ##temp_table FROM (
    SELECT a.Name FROM table AS a
    LEFT JOIN table AS b
    ON a.NAME = b.NAME
)

如果您有更多的专栏怎么办?好的，您只需在JOIN中显示其中一张表

And what if you have more colums? Ok, you can just show one of the tables in the JOIN

SELECT * INTO ##temp_table FROM (
    SELECT b.* FROM table AS a
    LEFT JOIN table AS b
    ON a.NAME = b.NAME
)

对不起，我的英语不好！希望对您有所帮助！

Sorry for my bad english! I hope this can help you!

这篇关于在SQL中将表联接到自身并保存结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！