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

问题描述

SQL Server 可以不要在 JOIN 的上下文中提供关键字 USING，
也不提供NATURAL JOIN.

除了明确(手动)列出所有列(链接到其他重复的问题)，是否有另一种方法来获取一个表，其中我加入的列在 2 个连接的表中具有相同的名称，但不重复?>

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

SELECT INTO #MyTempTable * FROM tableA内连接表BON tableA.commonColumn = tableB.commonColumn;

但我已经收到一个错误:

<块引用>

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

解决方案

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

DECLARE @columnsA varchar(8000)SELECT @columnsA = ISNULL(@columnsA + ', ','') + QUOTENAME(column_name)来自 INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = 'tableA' AND COLUMN_NAME <>'公共列'按 ORDINAL_POSITION 排序声明@columnsB varchar(8000)SELECT @columnsB = ISNULL(@columnsB + ', ','') + QUOTENAME(column_name)来自 INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = 'tableB' AND COLUMN_NAME <>'公共列'按 ORDINAL_POSITION 排序

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

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

所以至少有一种方法可以做到.:) 显然它的效率也适中.我不是 SQL 专家，但我想有一种方法可以从中创建一个函数，也许一个函数可以选择除 [...] 之外的所有列"，还有一个函数可以作为 USING 进行连接 可以.

---

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

声明@columnsB varchar(8000)SELECT @columnsB = ISNULL(@columnsB + ', ','') + QUOTENAME(table_name) + '.'+ QUOTENAME(column_name)来自 INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = 'tableB' AND COLUMN_NAME <>'公共列'按 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屋！