如何在 WHERE 子句中引用别名? [英] How do I reference an alias in a WHERE clause?

问题描述

这是我的声明:

SELECT 
  C.Account, 
  (RTRIM(N.FIRST) + ' ' + RTRIM(LTRIM(N.MIDDLE)) + ' ' + RTRIM(LTRIM(N.LAST)) + ' ' + LTRIM(N.SUFFIX)) AS OwnerName,
  DateAdd(dd, -1, C.ExpirationDate) as RealExpirationDate, 
  C.Description, 
  C.Type
FROM CARD as C
  INNER JOIN NAME as N ON C.Account = N.Account
WHERE (RealExpirationDate BETWEEN @StartDate AND @EndDate)
  AND C.Type IN(10,15,17,25)

我不断收到错误消息，指出 RealExpirationDate 是无效的列名.我如何引用该别名?

I keep getting an error saying that RealExpirationDate is an invalid column name. How can I reference that alias?

推荐答案

你不能在上面的代码中，记住 WHERE 发生在 SELECT 之前，所以你会必须使用:

You can't in your code above, remember WHERE happens before SELECT, so you'd have to use:

WHERE DateAdd(dd, -1, C.ExpirationDate) BETWEEN @StartDate AND @EndDate

为这样的东西设置别名的最常见方法是一些内部视图/查询，如下所示:

The most common way to alias something like this would be some inner view / query like so:

SELECT
  n.FooBar,  --here we can use FooBar
  t.BarFoo
FROM
  MyTable t
INNER JOIN
(
 SELECT
   myTestCase as FooBar
 From MyTable2
) n

这篇关于如何在 WHERE 子句中引用别名?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！