如何使用MySQL列别名进行计算? [英] How to use a MySQL column alias for calculations?
问题描述
如何使用两个子查询中的列别名(lat和lng)进行下面的距离计算?我基本上想做的是使用经度和纬度值计算两个位置之间的距离。但是为什么我的别名在查询中不可用,为什么呢?
How can I use my column alias (lat and lng) from the two subqueries to make the distance calcuation underneath? What I am basically trying to do is is to calculate the distance between two locations using longitude and latitude values. But somehow my aliases aren't usable in the query, why?
SELECT wp_posts.*,
(SELECT wp_postmeta.meta_value FROM wp_postmeta WHERE wp_postmeta.post_id = wp_posts.ID AND wp_postmeta.meta_value LIKE '41.%') AS lat,
(SELECT wp_postmeta.meta_value FROM wp_postmeta WHERE wp_postmeta.post_id = wp_posts.ID AND wp_postmeta.meta_value LIKE '2.%') AS lng,
(3959 * acos( cos( radians(41.367682) ) * cos( radians( 'lat' ) ) * cos( radians('lng') - radians(2.154077)) + sin(radians(41.367682)) * sin( radians('lat')))) AS distance
FROM wp_posts, wp_postmeta
WHERE wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = 'position' AND wp_posts.post_status = 'publish' AND wp_posts.post_type = 'page' AND wp_posts.post_date < NOW()
GROUP BY ID
ORDER BY distance ASC
推荐答案
好,您需要做的是使用不同的别名将同一张表(wp_postmeta)连接两次,以便可以使用不同的 WHERE条件。我没有您的桌子,所以无法测试,但这是您要使用的方法:
OK, What you need to do here is join the same table (wp_postmeta) twice under different aliases so you can use different 'WHERE' conditions. I don't have your tables so I can't test this, but this is the approach you'll want to use:
SELECT wp_posts.*,
(`alias_1`.meta_value) AS `lat`,
(`alias_2`.meta_value) AS `lng`,
(3959 * acos( cos( radians(41.367682) ) * cos( radians( `lat` ) ) * cos( radians( `lng` ) - radians(2.154077)) + sin(radians(41.367682)) * sin( radians( `lat` )))) AS `distance`
FROM wp_posts
LEFT JOIN `wp_postmeta` AS `alias_1` ON wp_posts.ID = alias_1.post_id
LEFT JOIN `wp_postmeta` AS `alias_2` ON wp_posts.ID = alias_2.post_id
WHERE
wp_posts.post_status = 'publish'
AND wp_posts.post_type = 'page'
AND wp_posts.post_date < NOW()
AND `alias_1`.meta_key = 'position'
AND `alias_1`.meta_value LIKE '41.%'
AND `alias_2`.meta_key = 'position'
AND `alias_2`.meta_value LIKE '2.%'
GROUP BY wp_posts.`ID`
ORDER BY `distance` ASC
我那里可能有一些语法错误,但我认为逻辑大致正确。让我知道这是否有效。
I may have some syntax errors in there, but I believe that the logic is roughly correct. Let me know if this works.
这篇关于如何使用MySQL列别名进行计算?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!