mysql - 'latitude' 中的未知列 'field list'

Question

SELECT GROUP_CONCAT(TRIM(postmeta.meta_value) SEPARATOR ",")
     , SUBSTRING_INDEX(GROUP_CONCAT(TRIM(postmeta.meta_value) SEPARATOR ","),",",1) latitude
     , SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TRIM(postmeta.meta_value) SEPARATOR ","),",",2),",",-1) longitude
     , ( 6371 * acos( cos( radians(52.2734487789) ) * cos( radians(latitude ) ) * cos( radians(longitude ) - radians(10.5438383386) ) + sin( radians(52.2734487789) ) * sin( radians(latitude ) ) ) ) AS distance 
  FROM `wp_umkreissuche_posts` AS posts
     ,  `wp_umkreissuche_postmeta` AS postmeta 
 WHERE posts.post_type = "adresses" 
   AND posts.ID=postmeta.post_id 
   AND postmeta.meta_key="latitude" 
    OR posts.post_type="adresses" 
   AND posts.ID=postmeta.post_id 
   AND postmeta.meta_key="longitude" 
 GROUP 
    BY postmeta.post_id 
HAVING distance < 0.5 
 ORDER 
    BY postmeta.post_id
     ,  distance ASC

Answer 1

这是您的查询，格式更好一些，并且具有更清晰的 join 语法:

SELECT GROUP_CONCAT(TRIM(postmeta.meta_value) SEPARATOR ","), 
       SUBSTRING_INDEX(GROUP_CONCAT(TRIM(postmeta.meta_value) SEPARATOR ","),",",1) AS latitude, 
       SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TRIM(postmeta.meta_value) SEPARATOR ","),",",2),",",-1) AS longitude,
       ( 6371 * acos( cos( radians(52.2734487789) ) * cos( radians(latitude ) ) * cos( radians(longitude ) - radians(10.5438383386) ) + sin( radians(52.2734487789) ) * sin( radians(latitude ) ) ) ) AS distance
FROM `wp_umkreissuche_posts` AS posts JOIN
     `wp_umkreissuche_postmeta` AS postmeta
     ON posts.ID = postmeta.post_id AND posts.post_type = 'adresses' AND
        (postmeta.meta_key IN ('latitude', 'longitude')
        )
GROUP BY postmeta.post_id
HAVING distance < 0.5
ORDER BY postmeta.post_id, distance ASC

您的直接问题是 latitude 和 longitude 是在 select 中定义的，因此它们不能在同一选择级别使用。正常的解决方案是重复表达式或使用子查询，因此别名是已知的。但是，您的查询的逻辑看起来并不可写。我怀疑它应该更像是这样的:

SELECT CONCATE_WS, ',', lat.postmeta.meta_value, lng.postmeta.meta_value) ,
       lat.postmeta.meta_value AS latitude, 
       lng.meta_value AS longitude,
       . . .
FROM `wp_umkreissuche_posts` posts JOIN
     `wp_umkreissuche_postmeta` lat
     ON posts.ID = postmeta.post_id AND posts.post_type = 'adresses' AND lat.meta_key = 'latitude' JOIN
     `wp_umkreissuche_postmeta` lng
     ON posts.ID = postmeta.post_id AND posts.post_type = 'adresses' AND lng.meta_key = 'longitude' 
HAVING distance < 0.5
ORDER BY postmeta.post_id, distance ASC;

注意:您可以在MySQL 中使用having而不使用group by。然后它的行为就像 where 但它允许列别名。