在SQL Server的JOIN中使用的替代方法，可避免联接表中的重复列 [英] Alternatives to USING in a JOIN for SQL Server for avoiding duplicated columns in joined table

问题描述

SQL Server 是在JOIN 的上下文中不提供关键字USING，
)，是否还有一种方法可以得到一个表，在该表中，我要连接的列(在2个连接的表中具有相同的名称)不重复?

作为中间步骤，我试图将结果保存在临时表中

SELECT INTO #MyTempTable * FROM tableA 
INNER JOIN tableB 
ON tableA.commonColumn = tableB.commonColumn;

但是我已经收到一个错误:

每个表中的列名必须唯一.表'#MyTempTable'中的列名'commonColumn'被多次指定.

解决方案

这并不容易.但是有可能.从此进行开发，您将首先创建两个单独的列表，其中包含2个表的所有列，但不包括其上的公共列您想加入:

DECLARE @columnsA varchar(8000)

SELECT @columnsA = ISNULL(@columnsA + ', ','') + QUOTENAME(column_name)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tableA' AND COLUMN_NAME <> 'commonColumn'
ORDER BY ORDINAL_POSITION

DECLARE @columnsB varchar(8000)

SELECT @columnsB = ISNULL(@columnsB + ', ','') + QUOTENAME(column_name)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tableB' AND COLUMN_NAME <> 'commonColumn'
ORDER BY ORDINAL_POSITION

然后，您将使用它们进行查询，只为其中一个表选择commonColumn:

EXEC ('SELECT tableA.commonColumn, ' + @columnA + ', ' + @columnsB + ' FROM tableA INNER JOIN tableB ON tableA.commonColumn = tableB.commonColumn;')

所以至少有一种方法可以做到这一点. :)显然也有一定效率.我不是SQL专家，但是我想有一种方法可以创建一个函数，也许有一个函数可以选择所有列，但[...]"，还有一个可以像USING那样进行连接的函数.做.

---

如果在清单中还添加包含表，则将变得更简单.这样，我们只需要从一个表中提取列名即可.

DECLARE @columnsB varchar(8000)

SELECT @columnsB = ISNULL(@columnsB + ', ','') + QUOTENAME(table_name) + '.' + QUOTENAME(column_name)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tableB' AND COLUMN_NAME <> 'commonColumn'
ORDER BY ORDINAL_POSITION

并将查询修改为:

EXEC ('SELECT tableA.* ', ' + @columnsB + ' FROM tableA INNER JOIN tableB ON tableA.commonColumn = tableB.commonColumn;')

SQL Server does not offer the keyword USING in the context of a JOIN,
nor it provides a NATURAL JOIN.

Besides explicitly (manually) listing all the columns (link to otherwise duplicated question), is there an alternative to obtain a table in which the columns I am joining onto, which have the same name in the 2 joined tables, are not duplicated?

As an intermediate step, I have tried to save in a temporary table the result

SELECT INTO #MyTempTable * FROM tableA 
INNER JOIN tableB 
ON tableA.commonColumn = tableB.commonColumn;

But I already get an error:

Column names in each table must be unique. Column name 'commonColumn' in table '#MyTempTable' is specified more than once.

解决方案

It is not easy. But it is possible. Developing from this, you would first create two separated lists with all the columns of the 2 tables, excluding the common column on which you want to join:

DECLARE @columnsA varchar(8000)

SELECT @columnsA = ISNULL(@columnsA + ', ','') + QUOTENAME(column_name)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tableA' AND COLUMN_NAME <> 'commonColumn'
ORDER BY ORDINAL_POSITION

DECLARE @columnsB varchar(8000)

SELECT @columnsB = ISNULL(@columnsB + ', ','') + QUOTENAME(column_name)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tableB' AND COLUMN_NAME <> 'commonColumn'
ORDER BY ORDINAL_POSITION

Then you would use them for your query, selecting the commonColumn only for one of the table:

EXEC ('SELECT tableA.commonColumn, ' + @columnA + ', ' + @columnsB + ' FROM tableA INNER JOIN tableB ON tableA.commonColumn = tableB.commonColumn;')

So there's at least one way to do it. :) It is apparently also moderately efficient. I am not an SQL expert, but I suppose there is a way to create a function out of this, maybe one function to "select all columns but [...]" and one function that would do the join as USING would do.

---

It becomes a little bit simpler if in listing we add also the containing table. In this way we need to extract only the column names from one table.

DECLARE @columnsB varchar(8000)

SELECT @columnsB = ISNULL(@columnsB + ', ','') + QUOTENAME(table_name) + '.' + QUOTENAME(column_name)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tableB' AND COLUMN_NAME <> 'commonColumn'
ORDER BY ORDINAL_POSITION

And the query is modified into:

EXEC ('SELECT tableA.* ', ' + @columnsB + ' FROM tableA INNER JOIN tableB ON tableA.commonColumn = tableB.commonColumn;')

这篇关于在SQL Server的JOIN中使用的替代方法，可避免联接表中的重复列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！