WHERE语句中无法识别列别名 [英] Column alias not recognized in WHERE-statement

问题描述

我创建的一个查询中有一个奇怪的问题".给出下一个查询:

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一直引发关于没有名为"LatestBookableTimestamp"的列的错误，即使我有一个由子查询检索的列，也就是该别名.我也尝试过使用tableprefix选择每一列.第八，这没有用.最后，我通过表别名选择了所有列，并为该表指定了别名.一切都没有运气.

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.

有人可以告诉我我做错了什么吗?我什至搜索了一些资源，看我是否记错了，但是在许多情况下，互联网上的作者使用的语法与我相同.

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.

推荐答案

使用HAVING

HAVING
  LatestBookableTimestamp < UNIX_TIMESTAMP()

另一方面，您使用的是依赖子查询，这对提高性能是一个坏主意.

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

尝试这样:

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()

这篇关于WHERE语句中无法识别列别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！