在SQL Server的JOIN中使用的替代方法，可避免联接表中的重复列

作为中间步骤，我试图将结果保存在临时表中SELECT INTO #MyTempTable * FROM tableAINNER JOIN tableBON tableA.commonColumn = tableB.commonColumn;但是我已经收到一个错误: 每个表中的列名必须唯一.表'#MyTempTable'中的列名'commonColumn'被多次指定.解决方案这并不容易.但是有可能.从此进行开发，您将首先创建两个单独的列表，其中包含2个表的所有列，但不包括其上的公共列您想加入:DECLARE @columnsA varchar(8000)SELECT @columnsA = ISNULL(@columnsA + ', ','') + QUOTENAME(column_name)FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = 'tableA' AND COLUMN_NAME <> 'commonColumn'ORDER BY ORDINAL_POSITIONDECLARE @columnsB varchar(8000)SELECT @columnsB = ISNULL(@columnsB + ', ','') + QUOTENAME(column_name)FROM INFORMATION_SCHEMA.COLUMNSWHERE 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.COLUMNSWHERE 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 tableAINNER JOIN tableBON 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.COLUMNSWHERE TABLE_NAME = 'tableA' AND COLUMN_NAME <> 'commonColumn'ORDER BY ORDINAL_POSITIONDECLARE @columnsB varchar(8000)SELECT @columnsB = ISNULL(@columnsB + ', ','') + QUOTENAME(column_name)FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = 'tableB' AND COLUMN_NAME <> 'commonColumn'ORDER BY ORDINAL_POSITIONThen 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.COLUMNSWHERE TABLE_NAME = 'tableB' AND COLUMN_NAME <> 'commonColumn'ORDER BY ORDINAL_POSITIONAnd the query is modified into:EXEC ('SELECT tableA.* ', ' + @columnsB + ' FROM tableA INNER JOIN tableB ON tableA.commonColumn = tableB.commonColumn;') 这篇关于在SQL Server的JOIN中使用的替代方法，可避免联接表中的重复列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 1403页，肝出来的..