为什么此 SQL 查询会导致“不明确的列名称”错误？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16342271/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why is this SQL Query causing an "Ambiguous column name" error?
提问by Jesse Webb
I have a SQL query which fails to execute:
我有一个无法执行的 SQL 查询：
select p.PersonID, CreatedDate, * from dbo.Person p
join dbo.PotentiallyFraudulentPeople pfp on p.PersonID= pfp.PersonID
order by CreatedDate
The Persontable has a PK of PersonID(int). The PotentiallyFraudulentPeopleview is a query of the Persontable joined with some other tables to decide if we trust a person or not. The PotentiallyFraudulentPeopleview only has one column: PersonID.
该Person表的 PK 为PersonID(int)。该PotentiallyFraudulentPeople视图是一个查询Person与其他表连接来决定，如果我们信任的人或不表。该PotentiallyFraudulentPeople视图只有一列：PersonID。
When I try to execute this query, I get this error:
当我尝试执行此查询时，出现此错误：
Msg 209, Level 16, State 1, Line 3
Ambiguous column name 'CreatedDate'.
消息 209，级别 16，状态 1，第 3
行列名称不明确“CreatedDate”。
I understand that this error is telling me that the CreatedDatecolumn name is ambiguous and I need to preface it with my table's alias, 'p'.
我知道这个错误告诉我CreatedDate列名不明确，我需要在它前面加上我的表别名“p”。
This query works:
此查询有效：
select p.PersonID, CreatedDate, * from dbo.Person p
join dbo.PotentiallyFraudulentPeople pfp on p.PersonID= pfp.PersonID
order by p.CreatedDate
What I don't understand is why I need to use the 'p' alias in the ORDER BYstatement and not in the SELECTcolumn list. Also, I don't understand why I need to use the table alias at all since the PotentiallyFraudulentPeopleview doesn't even have a CreatedDatecolumn.
我不明白的是为什么我需要在ORDER BY语句中而不是在SELECT列列表中使用“p”别名。另外，我不明白为什么我需要使用表别名，因为PotentiallyFraudulentPeople视图甚至没有CreatedDate列。
Can anyone explain this odd behavior?
谁能解释这种奇怪的行为？
I am using SQL Server 2008 and SSMS to execute the query.
我使用 SQL Server 2008 和 SSMS 来执行查询。
UPDATE
Also, I tried removing the CreatedDatecolumn from my SELECTcolumn list and then the query no longer requires the 'p' alias in the ORDER BY. So this query works as well:
更新
另外，我尝试CreatedDate从我的SELECT列列表中删除该列，然后查询不再需要 .p 文件中的“p”别名ORDER BY。所以这个查询也有效：
select p.PersonID, * from dbo.Person p
join dbo.PotentiallyFraudulentPeople pfp on p.PersonID= pfp.PersonID
order by CreatedDate
回答by Daniel Hilgarth
You are selecting the CreatedDatecolumn twice.
您正在选择该CreatedDate列两次。
- Explicitly via
CreatedDate. - Implicitly via
*.
- 明确通过
CreatedDate. - 隐式通过
*.
It doesn't know which occurence you want to sort on - and it obviously doesn't realize that both occurences refer to the same column.
它不知道您想对哪个出现进行排序 - 并且它显然没有意识到两个出现都指的是同一列。
回答by Bill Melius
And the reason that the name is ambiguous is related to the fact that the order by clause allows use of alias names so p.CreateDt and *.CreateDt both alias to CreateDt. Since order by allows alias names, it can't resolve
名称不明确的原因与 order by 子句允许使用别名有关，因此 p.CreateDt 和 *.CreateDt 都是 CreateDt 的别名。由于 order by 允许别名，因此无法解析
回答by Manuel Guarniz Cruz
You can add an alias to "CreatedDate" and sort it using the alias
您可以为“CreatedDate”添加别名并使用别名对其进行排序
select
p.PersonID,
CreatedDate [create_date],
*
from
dbo.Person p join
dbo.PotentiallyFraudulentPeople pfp on p.PersonID= pfp.PersonID
order by
[create_date]
回答by Abde
You have to specify which table that CreateDate came from. It means you have this column name in both tables. So either p.CreatedDate or pfp.CreatedDate.
您必须指定 CreateDate 来自哪个表。这意味着您在两个表中都有这个列名。所以要么 p.CreatedDate 要么 pfp.CreatedDate。
