MySQL 列 AS 与 BETWEEN 导致错误
我有一个问题,我需要在 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 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
来自 MySQL 文档:
您必须在
WHERE
子句中使用ID
而不是post_id
,或者使用HAVING
子句。例如:
更新:根据您最近的更新,您可以按照已经建议的方式使用子查询,或者按照我之前的建议,您可以使用
HAVING
子句。例如:
From the MySQL documentation:
You'll either have to use
ID
instead ofpost_id
in theWHERE
clause or use aHAVING
clause.For example:
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:
标准 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)
是的,你可以!
编辑以包括对问题的更改。试试这个(还没有在mysql中运行过,但如果不正确的话应该接近):
Yes, you can!
Edited to include changes to question. Try this (haven't run in mysql, but should be close if not correct):