带有 BETWEEN 的 MySQL 列 AS 导致错误

Question

我有一个问题，我需要在 MySQL 查询中对列进行 AS 处理，然后在该列上执行 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

Answer 1

来自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.

您必须在 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