在WHERE语句中无法识别列别名

I have a strange 'problem' with one of my created queries. Given the next query:

我对我创建的一个查询有一个奇怪的'问题'。鉴于下一个查询：

 SELECT 
 ID,
 DistanceFromUtrecht,
 (
  SELECT
   (MAX(DateUntil) - (ReleaseDays * 60 * 60 * 24))
  FROM
   PricePeriod
  WHERE
   PricePeriod.FK_Accommodation = Accommodation.ID
 ) AS LatestBookableTimestamp
FROM
 Accommodation
WHERE
 LatestBookableTimestamp < UNIX_TIMESTAMP()

phpMyAdmin keeps throwing an error about not having a column named 'LatestBookableTimestamp', even allthough I've a column, retreived by a subquery, that alias. I've also tried it selecting every column with the tableprefix. This didn't work eighter. Finally I've selected all columns by a table-alias and I gave the table an alias. All with no luck.

phpMyAdmin不断抛出一个关于没有名为'LatestBookableTimestamp'的列的错误，即使我有一个列，由一个子查询检索，该别名。我也试过用tableprefix选择每一列。这不起作用。最后，我通过表别名选择了所有列，并为表提供了别名。一切都没有运气。

Can someone tell me what I'm doing wrong? I've even searched for some resources to see if I'm not mistaken, but in many cases authors on the internet use the same syntax as I do.

有人能告诉我我做错了什么吗？我甚至搜索了一些资源，看看我是不是错了，但在很多情况下，互联网上的作者使用与我相同的语法。

4 个解决方案

#1

Use HAVING

使用HAVING

HAVING
  LatestBookableTimestamp < UNIX_TIMESTAMP()

On a side note, you're using a dependednt subquery, which is a bad idea performance wise.

另外，你正在使用一个依赖子查询，这在性能上是个坏主意。

Try like this:

试试这样：

SELECT 
  a.ID,
  a.DistanceFromUtrecht,
  pp.LatestBookableTimestamp
FROM
  Accommodation AS a
INNER JOIN (
  SELECT
    FK_Accommodation,
    MAX(DateUntil) - (ReleaseDays * 60 * 60 * 24) AS LatestBookableTimestamp
  FROM 
    PricePeriod 
  GROUP BY 
    FK_Accommodation
) AS pp    
ON pp.FK_Accommodation = a.ID    
WHERE
  pp.LatestBookableTimestamp < UNIX_TIMESTAMP()

#2

You can't use a column alias in the WHERE clause.
MySQL (and SQL Server) will allow column alias use in the GROUP BY, but it's not widely supported. ORDER BY is the most consistently supported place that supports column alias referencing.

您不能在WHERE子句中使用列别名。 MySQL（和SQL Server）将允许在GROUP BY中使用列别名，但它不受广泛支持。 ORDER BY是支持列别名引用的最一致支持的位置。

Use:

使用：

   SELECT a.id,
          a.distancefromutrecht,
          b.LatestBookableTimestamp
     FROM ACCOMMODATION a
LEFT JOIN (SELECT pp.fk_accommodation,
                  MAX(DateUntil) - (ReleaseDays * 60 * 60 * 24) AS LatestBookableTimestamp
             FROM PRICEPERIOD pp
         GROUP BY pp.fk_accommodation) b ON b.fk_accommodation = a.id
    WHERE b.LatestBookableTimestamp < UNIX_TIMESTAMP()

#3

You would need to take the original query, without the where clause, and turn that into a sub query.

您需要在没有where子句的情况下获取原始查询，并将其转换为子查询。

select * from (
 SELECT ID, DistanceFromUtrecht, 
 ( 
  SELECT 
   (MAX(DateUntil) - (ReleaseDays * 60 * 60 * 24)) 
  FROM 
   PricePeriod 
  WHERE 
   PricePeriod.FK_Accommodation = Accommodation.ID 
 ) AS LatestBookableTimestamp 
FROM 
 Accommodation 
) a
WHERE 
 LatestBookableTimestamp < UNIX_TIMESTAMP()

#4

select a.ID, 
    a.DistanceFromUtrecht, MaxDateUntil - (ReleaseDays * 60 * 60 * 24) AS LatestBookableTimestamp 
from (
    SELECT FK_Accommodation, MAX(DateUntil) as MaxDateUntil
    FROM PricePeriod 
    group by FK_Accommodation
) ppm
inner join Accommodation a on ppm.FK_Accommodation = a.ID 
where MaxDateUntil - (ReleaseDays * 60 * 60 * 24) < UNIX_TIMESTAMP()

#1