WHERE 语句中无法识别列别名

发布于 2024-09-15 19:42:16 字数 553 浏览 17 评论 0原文

我创建的查询之一遇到了一个奇怪的“问题”。给出下一个查询：

 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”的列的错误，尽管我有一个由子查询检索的列，该别名。我还尝试过选择带有表前缀的每一列。这没有用八。最后，我通过表别名选择了所有列，并给了表一个别名。一切都没有运气。

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

原文

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.

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

ぽ尐不点ル 2024-09-22 19:42:16

使用 HAVING

HAVING
  LatestBookableTimestamp < UNIX_TIMESTAMP()

顺便说一句，您正在使用依赖子查询，这在性能方面是一个坏主意。

尝试这样：

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

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

回复收藏 0 原文

寂寞清仓 2024-09-22 19:42:16

不能在 WHERE 子句中使用列别名。
MySQL（和 SQL Server）将允许在 GROUP BY 中使用列别名，但并未得到广泛支持。 ORDER BY 是支持列别名引用最一致的地方。

使用：

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

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.

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

回复收藏 0 原文

余厌 2024-09-22 19:42:16

您需要采用不带 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()

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

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

回复收藏 0 原文

琉璃梦幻 2024-09-22 19:42:16

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

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

回复收藏 0 原文

~没有更多了~