我目前正在開發一個 Wordpress/PHP/MySQL 專案。
我想運行一個查詢來查找地理位置接近登錄用戶的人。
為此，我在usermeta表中為用戶提供了兩個自定義欄位，緯度和經度，如下所示：
我想生成一個 SQL 查詢，其中“45.7699”是用戶的緯度，“4.8037”是他們的經度：
SELECT lat AS lat, lng AS lng, (6366*acos(cos(radians(45.7699))*cos(radians(lat))*cos(radians(lng) -radians(4.8037)) sin(radians(45.7699))*sin(radians(lat)))) AS dist FROM air_usermeta WHERE (6366*acos(cos(radians(45.7699))*cos(radians(lat))*cos(radians(lng) -radians(4.8037)) sin(radians(45.7699))*sin(radians(lat))))<='50' ORDER by dist ASC
我的問題一開始是：
SELECT lat AS lat, lng AS lng
如何在 lat 和 lng 欄位不是列而是 meta_value 的 Wordpress 背景關系中檢索值。
非常感謝你的幫助。
[解決]
SELECT a.user_id, a.meta_value AS lat, b.meta_value AS lng FROM air_usermeta as a, air_usermeta as b WHERE a.meta_key = "lat" AND b.meta_key= "lng" AND a.user_id = b.user_id AND (6366*acos(cos(radians(45.7699))*cos(radians(a.meta_value))*cos(radians(b.meta_value) -radians(4.8037)) sin(radians(45.7699))*sin(radians(a.meta_value))))<='20' ORDER by (6366*acos(cos(radians(45.7699))*cos(radians(a.meta_value))*cos(radians(b.meta_value) -radians(4.8037)) sin(radians(45.7699))*sin(radians(a.meta_value)))) ASC
uj5u.com熱心網友回復：
您可以使用自我加入來選擇它
SELECT a.id, a.meta_value as lat, b.meta_value as lng
FROM lbt_postmeta as a,
lbt_postmeta as b
WHERE a.meta_key = "lat"
AND b.meta_key= "lng"
AND a.id = b.id
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/458872.html
上一篇：INNERJOIN上的頂部
下一篇：從包含XML的列中獲取值