为什么此 SQL 查询会导致“不明确的列名"?错误? [英] Why is this SQL Query causing an "Ambiguous column name" error?

问题描述

我有一个无法执行的 SQL 查询:

I have a SQL query which fails to execute:

select p.PersonID, CreatedDate, * from dbo.Person p 
join dbo.PotentiallyFraudulentPeople pfp on p.PersonID= pfp.PersonID 
order by CreatedDate 

Person 表具有 PersonID (int) 的 PK.PotentiallyFraudulentPeople 视图是对 Person 表和其他一些表的查询，以确定我们是否信任一个人.PotentiallyFraudulentPeople 视图只有一列:PersonID.

The Person table has a PK of PersonID (int). The PotentiallyFraudulentPeople view is a query of the Person table joined with some other tables to decide if we trust a person or not. The PotentiallyFraudulentPeople view only has one column: PersonID.

当我尝试执行此查询时，出现此错误:

When I try to execute this query, I get this error:

消息 209，级别 16，状态 1，第 3 行
不明确的列名称CreatedDate".

Msg 209, Level 16, State 1, Line 3
Ambiguous column name 'CreatedDate'.

我知道这个错误告诉我 CreatedDate 列名称不明确，我需要在它前面加上我的表别名p".

I understand that this error is telling me that the CreatedDate column name is ambiguous and I need to preface it with my table's alias, 'p'.

此查询有效:

select p.PersonID, CreatedDate, * from dbo.Person p 
join dbo.PotentiallyFraudulentPeople pfp on p.PersonID= pfp.PersonID 
order by p.CreatedDate 

我不明白的是为什么我需要在 ORDER BY 语句中而不是在 SELECT 列列表中使用 'p' 别名.另外，我不明白为什么我需要使用表别名，因为 PotentiallyFraudulentPeople 视图甚至没有 CreatedDate 列.

What I don't understand is why I need to use the 'p' alias in the ORDER BY statement and not in the SELECT column list. Also, I don't understand why I need to use the table alias at all since the PotentiallyFraudulentPeople view doesn't even have a CreatedDate column.

谁能解释这种奇怪的行为?

Can anyone explain this odd behavior?

我使用 SQL Server 2008 和 SSMS 来执行查询.

I am using SQL Server 2008 and SSMS to execute the query.

更新
此外，我尝试从我的 SELECT 列列表中删除 CreatedDate 列，然后查询不再需要 ORDER BY 中的p"别名.所以这个查询也有效:

UPDATE
Also, I tried removing the CreatedDate column from my SELECT column list and then the query no longer requires the 'p' alias in the ORDER BY. So this query works as well:

select p.PersonID, * from dbo.Person p 
join dbo.PotentiallyFraudulentPeople pfp on p.PersonID= pfp.PersonID 
order by CreatedDate 

推荐答案

您选择了两次 CreatedDate 列.

1. 显式通过 CreatedDate.
2. 隐式通过 *.

它不知道您想对哪个出现进行排序 - 并且它显然没有意识到这两个出现指的是同一列.

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.

这篇关于为什么此 SQL 查询会导致“不明确的列名"?错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！