如何删除/重命名 SQL 中的重复列(非重复行) [英] How to remove/rename a duplicate column in SQL (not duplicate rows)

问题描述

尝试从 Sybase 到 Microsoft SQL 执行 OPENQUERY 时遇到错误:

When attempting to do an OPENQUERY from Sybase to Microsoft SQL I run into an error:

通过以下方式获得的结果集中不允许出现重复的列名OPENQUERY 和 OPENROWSET.列名称PatientID"是重复的.

Duplicate column names are not allowed in result sets obtained through OPENQUERY and OPENROWSET. The column name "PatientID" is a duplicate.

我构建的查询基于相似的准入 ID 和患者 ID 连接了 2 个表.

The query I have built joins 2 tables based on similar admissionID and patientID.

例如:

PatID   AdmID   Loc  PatID  AdmID   Doctor 
1         5      NC    1      5      Smith 
2         7      SC    2      7      Johnson

当然，真正的查询包含的信息远不止这些.

The real query of course has a ton more information than just this.

是否有重命名或删除 AdmID 和 PatID 列之一的好方法?

Is there a good way to rename or remove one of the AdmID and PatID columns?

我尝试过:

SELECT * INTO #tempTable
ALTER #tempTable
DROP COLUMN PatID

这不起作用，因为 PatID 不明确.

This does not work since PatID is ambiguous.

我也尝试过:

SELECT firstTable.PatID as 'pID', * FROM...

这也不起作用.

推荐答案

您必须为两个重复列之一设置别名，并至少为其中一个表(您已经在它的列上使用别名):

You'll have to alias one of the two duplicate columns and explicitly indicate specific columns in the select at least for one of the tables (the one you've used the alias on it's column):

 SELECT firstTable.PatID as 'pID', firstTable.column2, secondTable.* FROM...

请注意，我仍然在 secondTable 上使用了通配符.

Notice, I've still used a wildcard on the secondTable.

不过……

我会避免完全使用 * 通配符，并尝试始终准确指出您需要的列.

I'd avoid using * wildcards altogether and try to always indicate exactly the columns you need.

这篇关于如何删除/重命名 SQL 中的重复列(非重复行)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！