MySQL 列 AS 与 BETWEEN 导致错误

发布于 2024-11-17 10:09:32 字数 894 浏览 8 评论 0原文

我有一个问题,我需要在 MySQL 查询中作为一列并在该列上执行 BETWEEN 。我能想到的说明问题的最简单版本是这样的:

SELECT ID AS post_id FROM wp_posts WHERE (post_id BETWEEN 10 AND 20)

这会引发以下错误:

#1054 - Unknown column 'post_id' in 'where clause'

有没有办法使新创建的 post_id 列对 BETWEEN 运算符可见?

更新:

这是我的实际查询。正如您所看到的,它稍微复杂一些:

SELECT wp_postmeta.post_id,
MAX(CASE WHEN wp_postmeta.meta_key='store_lng' THEN wp_postmeta.meta_value END ) AS lng,
MAX(CASE WHEN wp_postmeta.meta_key='store_lat' THEN wp_postmeta.meta_value END ) AS lat
FROM wp_postmeta
LEFT JOIN wp_posts ON (wp_posts.ID=wp_postmeta.post_id) 
WHERE
(lng BETWEEN 150.29793837662 AND 152.1161201948)
AND 
(lat BETWEEN -34.775666623377 AND -32.957484805195) 
AND
wp_posts.post_status='publish'
AND
wp_posts.post_type='store'
GROUP BY wp_postmeta.post_id

I have an issue where I need to AS a column in a MySQL query and execute a BETWEEN on that column. The simplest version I can come up with that illustrates the problem is this:

SELECT ID AS post_id FROM wp_posts WHERE (post_id BETWEEN 10 AND 20)

This throws the following error:

#1054 - Unknown column 'post_id' in 'where clause'

Is there a way to make the newly created post_id column visible to the BETWEEN operator?

UPDATE:

This is my actual query. As you can see, it is slightly more complicated:

SELECT wp_postmeta.post_id,
MAX(CASE WHEN wp_postmeta.meta_key='store_lng' THEN wp_postmeta.meta_value END ) AS lng,
MAX(CASE WHEN wp_postmeta.meta_key='store_lat' THEN wp_postmeta.meta_value END ) AS lat
FROM wp_postmeta
LEFT JOIN wp_posts ON (wp_posts.ID=wp_postmeta.post_id) 
WHERE
(lng BETWEEN 150.29793837662 AND 152.1161201948)
AND 
(lat BETWEEN -34.775666623377 AND -32.957484805195) 
AND
wp_posts.post_status='publish'
AND
wp_posts.post_type='store'
GROUP BY wp_postmeta.post_id

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

扫码二维码加入Web技术交流群

发布评论

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

评论(3

小巷里的女流氓 2024-11-24 10:09:32

来自 MySQL 文档

不允许引用
WHERE 子句中的列别名,
因为列值可能还没有
当 WHERE 子句为
已执行。

您必须在 WHERE 子句中使用 ID 而不是 post_id,或者使用 HAVING 子句。

例如:

SELECT ID AS post_id FROM wp_posts WHERE ID BETWEEN 10 AND 20

更新:根据您最近的更新,您可以按照已经建议的方式使用子查询,或者按照我之前的建议,您可以使用 HAVING 子句。

例如:

SELECT    wp_postmeta.post_id,
          MAX(CASE WHEN wp_postmeta.meta_key = 'store_lng' THEN wp_postmeta.meta_value END) AS lng,
          MAX(CASE WHEN wp_postmeta.meta_key = 'store_lat' THEN wp_postmeta.meta_value END) AS lat
FROM      wp_postmeta
LEFT JOIN wp_posts
ON        wp_posts.ID = wp_postmeta.post_id
WHERE     wp_posts.post_status = 'publish' AND
          wp_posts.post_type = 'store'
GROUP BY  wp_postmeta.post_id
HAVING    lng BETWEEN 150.29793837662 AND 152.1161201948 AND 
          lat BETWEEN -34.775666623377 AND -32.957484805195

From the MySQL documentation:

It is not permissible to refer to a
column alias in a WHERE clause,
because the column value might not yet
be determined when the WHERE clause is
executed.

You'll either have to use ID instead of post_id in the WHERE clause or use a HAVING clause.

For example:

SELECT ID AS post_id FROM wp_posts WHERE ID BETWEEN 10 AND 20

Update: Based on your most recent update, you could use a subquery as already suggested or as I suggested earlier, you could use a HAVING clause.

For example:

SELECT    wp_postmeta.post_id,
          MAX(CASE WHEN wp_postmeta.meta_key = 'store_lng' THEN wp_postmeta.meta_value END) AS lng,
          MAX(CASE WHEN wp_postmeta.meta_key = 'store_lat' THEN wp_postmeta.meta_value END) AS lat
FROM      wp_postmeta
LEFT JOIN wp_posts
ON        wp_posts.ID = wp_postmeta.post_id
WHERE     wp_posts.post_status = 'publish' AND
          wp_posts.post_type = 'store'
GROUP BY  wp_postmeta.post_id
HAVING    lng BETWEEN 150.29793837662 AND 152.1161201948 AND 
          lat BETWEEN -34.775666623377 AND -32.957484805195
绅士风度i 2024-11-24 10:09:32

标准 SQL 不允许在 WHERE 子句中引用列别名。

施加此限制是因为当计算 WHERE 子句时,列值可能尚未确定。
(http://dev.mysql.com/doc/ refman/5.0/en/problems-with-alias.html

Standard SQL disallows references to column aliases in a WHERE clause.

This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.
(http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html)

明媚殇 2024-11-24 10:09:32

是的,你可以!

SELECT post_id
from (select id AS post_id FROM wp_posts) x
WHERE post_id BETWEEN 10 AND 20

编辑以包括对问题的更改。试试这个(还没有在mysql中运行过,但如果不正确的话应该接近):

SELECT *
FROM (SELECT
  wp_postmeta.post_id,
  MAX(CASE WHEN wp_postmeta.meta_key='store_lng' THEN wp_postmeta.meta_value END ) AS lng,
  MAX(CASE WHEN wp_postmeta.meta_key='store_lat' THEN wp_postmeta.meta_value END ) AS lat 
FROM wp_postmeta
GROUP BY wp_postmeta.post_id
) x
LEFT JOIN wp_posts ON wp_posts.ID = x.post_id
WHERE lng BETWEEN 150.29793837662 AND 152.1161201948
AND lat BETWEEN -34.775666623377 AND -32.957484805195
AND wp_posts.post_status='publish'
AND wp_posts.post_type='store'

Yes, you can!

SELECT post_id
from (select id AS post_id FROM wp_posts) x
WHERE post_id BETWEEN 10 AND 20

Edited to include changes to question. Try this (haven't run in mysql, but should be close if not correct):

SELECT *
FROM (SELECT
  wp_postmeta.post_id,
  MAX(CASE WHEN wp_postmeta.meta_key='store_lng' THEN wp_postmeta.meta_value END ) AS lng,
  MAX(CASE WHEN wp_postmeta.meta_key='store_lat' THEN wp_postmeta.meta_value END ) AS lat 
FROM wp_postmeta
GROUP BY wp_postmeta.post_id
) x
LEFT JOIN wp_posts ON wp_posts.ID = x.post_id
WHERE lng BETWEEN 150.29793837662 AND 152.1161201948
AND lat BETWEEN -34.775666623377 AND -32.957484805195
AND wp_posts.post_status='publish'
AND wp_posts.post_type='store'
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文