SQL 别名给出无效的列名 [英] SQL alias gives invalid column name

问题描述

使用以下 SQL 表达式，但出现错误.

Using the following SQL expression but I'm getting an error.

select
  CampaignCustomer.CampaignCustomerID,
  convert(varchar, CampaignCustomer.ModifiedDate, 111) as startdate,
  CampaignCustomer.CampaignID,
  CampaignCustomer.CampaignCallStatusID,
  CampaignCustomer.UserID,
  CampaignCustomerSale.Value,
  Users.Name
from CampaignCustomer
  inner join CampaignCustomerSale
    on CampaignCustomer.CampaignCustomerID = CampaignCustomerSale.CampaignCustomerID
  inner join Users
    on CampaignCustomer.UserID = Users.UserID
where
  CampaignCustomer.CampaignCallStatusID = 21
  and CampaignCustomer.startdate = '2011/11/22'      <------- THIS
order by
  startdate desc,
  Users.Name asc

错误:

消息 207，级别 16，状态 1，第 1 行
无效的列名开始日期".

Msg 207, Level 16, State 1, Line 1
Invalid column name 'startdate'.

我无法在 WHERE 子句中识别我的别名 startdate，但它可以在我的 ORDER BY 子句中.怎么了?

I can't recognize my alias name startdate in the WHERE clause, but it can in my ORDER BY clause. What's wrong?

不，我不可能将数据类型更改为 date 而不是 datetime.其他地方需要时间.但在这种情况下，我只需要获取特定日期的所有帖子，我真的不关心 modifieddate 的日期时间是 :)

And no, it is not possible for me to change the datatype to date instead of datetime. The time is needed elsewhere. But in this case, I need only to get all posts on a specific date and I really don't care about what time of the date the modifieddate is :)

也许需要另一种方法来代替 convert()?

Maybe another method is needed instead of convert()?

推荐答案

WHERE 子句中不能使用列别名.

You can't use column alias in WHERE clause.

改为:

where
  CampaignCustomer.CampaignCallStatusID = 21
  and convert(varchar, CampaignCustomer.ModifiedDate, 111) = '2011/11/22' 

这篇关于SQL 别名给出无效的列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！